-
1. Re: help with splitting an inconsistent string?
Simon RuncJun 15, 2018 11:42 AM (in response to rachel.kell.1)
Hi Rachel,
Sound like a tough one!!
In the attached I first worked out how many dashes each description has
[Number of Dashes]
LEN([Building Full Description])
-
LEN(REPLACE([Building Full Description], '-',''))
and then used this to work out which bit we needed to grab
[Short Description]
IF [Number of Dashes] = 1
THEN SPLIT([Building Full Description],'-',1)
ELSE
LEFT([Building Full Description],FINDNTH([Building Full Description],'-',2)-1)
END
Hope that covers all cases, but if not if you can let me have a few examples where it fails, I can add a bit of extra (handling) logic to the formula
-
2. Re: help with splitting an inconsistent string?
rachel.kell.1 Jun 15, 2018 10:36 AM (in response to Simon Runc)oooo, this is definitely part way there! thank you!!
some issues:
a) where there are NO dashes I get NULL i.e. 9 Winter Street --> Null
b) where there is one dash in a number I just get the first number, i.e. 9-22 Winter Street --> 9
c)where there are 2 dashes and none in the initial number i also pick up the room number, i.e. 9 Winter Street - Rm 101 - Auditorium --> 9 Winter Street - Rm 101
It works perfectly when I have something like
d) 9-22 Winter Street - Rm 101 - Auditorium
OR
e) 9 Winter Street - Rm 101
I was able to address the NO dashes issue (a) pretty easily:
IF ([Number of Dashes] = 0)
THEN [Building]
ELSEIF ([Number of Dashes] = 1)
THEN split ([Building],'-',1)
ELSE LEFT([Building],findnth([Building],'-',2)-1)
END
thoughts on b and c? maybe it's b/c dashes in the description have spaces but dashes in the initial number don't?
-
3. Re: help with splitting an inconsistent string?
Yuriy FalJun 15, 2018 10:48 AM (in response to Simon Runc)
1 of 1 people found this helpfulSimon, imho the ' - ' (a dash surrounded by two blanks)
would be a better splitter -- and your calc logic is flawless :-)
-
4. Re: help with splitting an inconsistent string?
rachel.kell.1 Jun 15, 2018 11:11 AM (in response to Yuriy Fal)this version
IF ([Number of Dashes] = 0)
THEN [Building]
ELSEIF ([Number of Dashes] = 1)
THEN split ([Building],' - ',1) <-- added space here
ELSE LEFT([Building],findnth([Building],'-',2)-1)
END
seems to work better.
I still have the issue of (c) which isn't ideal but it's definitely an improvement!!
-
5. Re: help with splitting an inconsistent string?
Yuriy FalJun 15, 2018 11:16 AM (in response to rachel.kell.1)
1 of 1 people found this helpfulI would be trying the simpler variant, too:
IF ([Number of Dashes] = 0)
THEN [Building]
ELSE split ([Building],' - ',1)
END
-
6. Re: help with splitting an inconsistent string?
rachel.kell.1 Jun 15, 2018 11:28 AM (in response to Yuriy Fal)yaaaasssss!
-
7. Re: help with splitting an inconsistent string?
Simon RuncJun 15, 2018 11:39 AM (in response to Yuriy Fal)
Nice work Yuri...didn't occur to me that building numbers (with dashes) wouldn't have the space padding!