Parsing out text using a formula 1

[tav1035]

I need a formula for crystal that would parse out the first word, then another for the second word and another for the third word.
The description will always be delimmited by commas.
My goal is to parse them into 3 different columns.

I can now do this in excel, but I want to do it crystal, before exporting to excel.

Here is the example for excel-
For this cell containing AIR CONDITIONING UNIT, SPLIT, COMPUTER ROOM UNIT

AIR CONDITIONING UNIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),1*255,255))

SPLIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),2*255,255))

COMPUTER ROOM UNIT
=TRIM(MID(SUBSTITUTE("," & $N1&REPT(",",6),",",REPT(" ",255)),3*255,255))

For the 3rd word change from 2*255 to 3*255

referenced

http://tek-tips.com/viewthread.cfm?qid=1675126

Thanks
tav

 

[briangriffin]

Text strings can be converted into arrays using the split function:

split({YourDBField},",")[1]

The [1] is the number of the element in the array; you'll need three formulas numbered [1], [2], and [3].

 

[tav1035]

The description for the first asset is SHREDDER, PAPER
This should come out as SHREDDER using the first formula (which it does)
This should come out as PAPER (has an error)


The first formula worked as suggested split({ASSET.DESCRIPTION},",")[1])
The second formula saved, but it has an error when adding as a field on my report. split({ASSET.DESCRIPTION},",")[2]
The error says "A subscript must be between 1 and the size of the array"

It's be because some descriptions are only one or two words.
The description is SHREDDER, PAPER only has 1 comma, so the formula using split({ASSET.DESCRIPTION},",")[2] won't work.
Some description may not contain a comma at all and other may contain 5 or more commas. Here is another description with multiple commas... GENERATOR, KOHLER, 600RODZ, YEAR, BLDG SUPPT, DIESEL

It must contain some if statments-
If the array contains no comma, return the {ASSET.DESCRIPTION}
If the array contains one comma, return the description prior to the comma.
If the array contains two commas, return the description prior to the second comma.

I just don't know how to write the if statments.
Thanks
tav

 

[briangriffin]

I think you have a typo; the first two formulas would work for a value of "SHREDDER, PAPER". The third formula would not, because there are only two elements in the array.

Trap for this by finding the number of elements using ubound:

//Second formula - first formula would work as is.

if ubound(split({@Asset.Description},",")) >= 2 then

split({Asset.Description},",")[2]

else ""

Change the 2's to 3's for the third formula, etc.

 

[tav1035]

Thanks Brian,
I used the suggested formulas as -

First formula-
split({ASSET.DESCRIPTION},",")[1]

Second formula-
if ubound(split({Asset.Description},",")) >= 2 then
split({Asset.Description},",")[2]
else ""

Third Formula-
if ubound(split({Asset.Description},",")) >= 3 then
split({Asset.Description},",")[3]
else ""

Everything looks good. Thanks again,
tav