7 Replies Latest reply on Jun 15, 2018 11:39 AM by Simon Runc

help with splitting an inconsistent string?

rachel.kell.1 Jun 15, 2018 9:29 AM

i have a field of building names and I need to make them shorter so my map is all purty....

essentially the format is BUILDING NAME - ROOM AND ROOM DESCRIPTION and I want to remove everything after the dash

the issue is that some buildings start with numbers, and some don't, and some are in this form: 11-20 STREET NAME and some even have dashes between the room and room description (!!!)

i've tried a bunch of split variations and i'm pulling my hair out.

any help is appreciated!

1. Re: help with splitting an inconsistent string?

Simon Runc Jun 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

Parse Out Name from Inconsistent Descriptions.twbx 14.5 KB
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
3. Re: help with splitting an inconsistent string?

Yuriy Fal Jun 15, 2018 10:48 AM (in response to Simon Runc)

Simon, imho the ' - ' (a dash surrounded by two blanks)
would be a better splitter -- and your calc logic is flawless :-)
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
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!!
Like Show 0 Likes(0)

Actions
5. Re: help with splitting an inconsistent string?

Yuriy Fal Jun 15, 2018 11:16 AM (in response to rachel.kell.1)

I would be trying the simpler variant, too:
IF ([Number of Dashes] = 0)
THEN [Building]
ELSE split ([Building],' - ',1)
END
1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions
6. Re: help with splitting an inconsistent string?

rachel.kell.1 Jun 15, 2018 11:28 AM (in response to Yuriy Fal)

yaaaasssss!
Like Show 1 Likes(1)

Actions
7. Re: help with splitting an inconsistent string?

Simon Runc Jun 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!
Like Show 1 Likes(1)

Actions