Splitting Text by comma

[wwyngaard]

I have text in a field that I need to break apart at the commas in the field.

Data looks like "White, 345 blue, black, 787 Gold"

I need to get White into a field, 345 Blue in the next field and so on.

Could someone help me with the calculation?

Thanks,

Wayne

[FUBAR]

This probably is not the best idea but may be faster than exporting into Excel and then importing the data back into FM (depending on the size of the field to be broke down into seperate fields.)

I have a similar situation where I need my field to be broken down into seperate fields whenever there is an enter symbol (when choosing more than one selection from a check box, FileMaker stores the selections by seperating them with the enter symbol)

I have a script and two global fields (char counter and char marker) I scan through the field character by character, find the enter symbol, and then set the appopriate field using the text between the enter symbol and wherever the char marker is. The loop is then broken when the char counter is equal to the length of the field.

You could use something similar to this if the only occurances of a comma are when you want to seperate the data.

Also, this could be greatly improved apon if there was a way to return the position of a specific occurence in a field...e.g

Field = test

Status (LocationOfText, "s") <---returns 3

But I'm not sure if FM can do this without a plug-in...if anyone knows how please yell at me to write my script a better way

[Kurt Knippel]

I would suggest bringing the data into Excel, which will very easily parse the data based upon the placement of the comma. Just export the field to a comma-seperated file. This will cause Excel to break the data into seperate fields, but keep the records the same.

I use Excel for data scrubbing all the time.

[danjacoby]

Look up the "position" command in calculations.

I won't describe it all here, but basically it lets you grab all the stuff to the left of the first comma, then all the stuff between the first and second commas, etc.

It's exactly what you need.

HTH,

Dan

[FUBAR]

Thanks for the tip Dan, hopefully this will clean up my scripts a bit

[SteveB]

Here's a technique that I call destructive string parsing. It saves you from having to keep track of where you are in the string and eliminates the most likely cause of errors:

If you need to retain the integrity of the original string, save a copy in a global (call it _Temp) and use the global.

Call PatternCount to get the number of separators in the string...generally one more than the function returns unless the last character is a separator. Now you have a count of the number of items to parse.

Use the Position function to find the location of the 1st separator. The end of the 1st field or answer is the position-1. Pull this off using Left(_Temp,position-1).

Here's the destructive part: SetField(_Temp = Right(_Temp,Length(_Temp)-position). In other words, the string = rest of the string. You don't need any pointers to the next field, etc.

Call Position again, etc.

You can put the whole thing in a loop if necessary and modularize it, getting the next field in a sub script. If it needs to be in a loop, use a counter that you either increment or decrement which is compared to the 1st use of the Pattercount above.

It actually very easy to do...probably harder to write about it than actually doing it since we only pull off the 1st answer every time.