Hello

I have a line in a field of text which needs to be separated into two fields.

field one and field two.

But the original text is separated by a "," or a ";" or by a ":" .

Every thing to the left of the comma, colon , or semi colon needs to go to field "one"

and everything to the right of the comma, colon , or semi colon needs to go to field "two"

Can someone out line how to do this for me.

Thanks

STAN

FileMaker Version: 7

Platform: Mac OS X Panther

There are several ways to do what you want! I would use the following:

You have three fields: <OrigText> This is your original text field, and two calculating fields, <TextLeft> which takes the value before the , : or ; and <TextRight>, which takes the value after. The first step is to grab the text to the left of the symbol, however, it makes things easier if we firstly convert the possible symbols to a comma using the substitute command, and also remove this comma from the result using the substitute command, so:

TextLeft =

Trim(Substitute(Left( OrigText; Position( Substitute( Substitute(OrigText, ":", ","),";",","), ",", 0, 1)), ",", ""))

So, for example, if in the field OrigText we had the value:

This is very good, The problems is that some of the original text has a comma that needs to be there. and some of the text is mistakenly separated by " :" or " ; " so it could be either a comma a colon or a semi colon, In which case I am not exactly sure what to do.

Thanks

Stan

No you are right I just got up and I didn't see you have all of my symbols covered but when I do your formula it says there are too few parameters. I keep trying different things but it is not accepting it.

Stan

Hi Stann,

I can't tell by your Bio information if you are using a US or Non US versions of FileMaker, but there appears to be a typo in the formula:

TextLeft =

Trim(Substitute(Left( OrigText; Position( Substitute( Substitute(OrigText, ":", ","),";",","), ",", 0, 1)), ",", ""))

If you are using US version, change the semicolon ";" following Orig Text in the first instance to a comma "," OR, if you are using an Non US version change the comma "," following Orig Text in the second instance to a semicolon ";" to a comma ",".

HTH

Lee

Was there ever a solution to the following quote?

stann said:

The problems is that some of the original text has a comma that needs to be there. and some of the text is mistakenly separated by " :" or " ; " so it could be either a comma a colon or a semi colon, In which case I am not exactly sure what to do.

In the lines where you need to keep the comma, is the comma always in one particular part of the field? Would you always want to keep the first comma and divide the field at the second comma/semicolon/colon?

For Example:

Las Vegas,NV:Mike Miller

Denver,CO;Bill Downing

St. Louis MO;Fran Felter

Without giving away trade secrets or real information (if you're concerned), could you include a couple examples?

Thanks! Paul

If the divider in the text field can be a comma, colon or semicolon, but these symbols can also occur where the text shouldn

I agree. If there are multiple punctuation marks throughout the text, we may be stuck. But, if the divider is always the final punctuation mark or is always in the same general pattern, there may be additional ways to approach the problem/solution. If nothing else, we could significantly cut down on the amount of hand-corrections that need to be done.

Besides, I'm obsessive/compulsive, and it's physically painful for me to say "We're stuck" without at least seeing some sample data and trying to hammer out the best solution.

Paul

If you're using FM7, you can do a substitute to change any comma, colon or semi-colon into new paragraph. Then you can use the leftvalues(original_field; 1) function to get the first line, and rightvalues(original_field; 1) to get the second line.

Rigsby said:

(PS. I had a semicolon instead of a comma in my calculation. I use a German version of FMP, so need to change the semicolons to commas, sorry)

I noticed the typo and figured it was something like that.

I often use Ugo's utility file "Translator", to help avoid this type of error. You can download a copy Here.

The file allows you to enter a Calculation in your native language ("US", "French", "German", "Spanish", "Dutch", "Swedish", or "Italian") and then convert it to any of the other languages that are available.

A real treat is the ability to "Customize" the field names. This allows you to use your field names, maybe those in one of your files so that you can just copy it. And than change them to fit the "Thread", either to the ones being used by the poster, or ones that better fit "Thread". In other words, maybe the poster used a field named "Start Date", and you have a calculation that your field name is Date_Start, (SDate, DateStart, EnterDate, etc.), you can "Customize" your field names to theirs, thereby making it easier for them to follow your thought process, and/or make it easier for them to just paste your calculation right into their field.

HTH

Lee

FileMaker Version: 6

Platform: Mac OS 9