Split String Field Based on Character (tilda)

[aj3221]

I have reviewed some of other posts and tried a few different formulas, but I still can't get this to work.

I have a string field that looks like this:
Janitorial Restroom ~ 1011-4 ~ Jan/San

I need it to be split into three separate fields using the tilda as a split.

CategoryName CategoryNo CategoryShortName
Janitorial Restroom 1011-4 Jan/San

Can you help?

Thanks so much!

 

[IanWaterman]

If its always 3 elements create 3 formula

@catname
Split({yourfield}, "~")[1]

@CatNum
Split({yourfield}, "~")[2]

@CatShort
Split({yourfield}, "~")[3]

Ian

 

[aj3221]

I'm sorry - I should have put in the original post that I tried that and get this error:

A subscription must be between 1 and the size of the array.

 

[fisheromacse]

please show the contents of your formula that returns the error.

is it possible that there is not a '~' for some records? or that some records do not have all 3 items?

 

[aj3221]

Some lines just have a space and a tilda because there isn't a reocrd.

I could have:
Janitorial Restroom ~ 1011-4 ~ Jan/San
or
Janitorial Restroom ~ ~ Jan/San
or
~ 1011-4 ~ Jan/San

 

[fisheromacse]

what do the contents of your formula look like? (edit formula and copy/paste it into this thread so that you can get more accurate help)
did you follow Ian's suggestion?

 

[aj3221]

I've tried a few. The first one I listed below is the active one I have in the report now that is kicking back the error.

Split({icsp.user14}, "~")[1]

extractstring({icsp.user14}," ~ ", " ~ ")

split(split({icsp.user14},"~")[2],"~")[2]

 

[lbass]

//{@seg1}:
if isnull({yourfield}) then
"" else
Split({yourfield}, "~")[1]

//{@seg2}:
if isnull({yourfield}) then
"" else
if ubound(Split({yourfield}, "~")>1 then
Split({yourfield}, "~")[2]

//{@seg3}:
if isnull({yourfield}) then
"" else
if ubound(Split({yourfield}, "~")>2 then
Split({yourfield}, "~")[3]

-LB

 

[aj3221]

When using seq 2 & seq 3 above it says: The ) is missing.

 

[aj3221]

I just added Seq 2 to the report and it's kicking back the "A subscript must be between 1 and the size of the array.

 

[lbass]

//{@seg2}:
if isnull({yourfield}) then
"" else
if ubound(Split({yourfield}, "~"))>1 then
Split({yourfield}, "~")[2]

//{@seg3}:
if isnull({yourfield}) then
"" else
if ubound(Split({yourfield}, "~"))>2 then
Split({yourfield}, "~")[3]

-LB