Splitting field contents into two or more fields

[DoniDavioni]

I am new to this forum and unfamiliar with the rules. I have FileMaker Pro 4.0 on the Mac. In my database I have a text field called "attn" which includes the first and last name of clients. I want to split the information into two fields, one for first name and one for last name. How can I do this?

DoniDavioni

[jscooper]

There are calculations to get the first, middle, last words of some text. Make a text calculation field called firstname and set it to:

leftwords(attn,1)

then make one called lastname and set it to:

rightwords(attn,1)

Of course, if there are two-part last names (Van Something, for example), the extra words (Van) will be lost. But you could do somthing like

lastname =

if(wordcount(attn) < 2,attn,rightwords(attn,wordcount(attn)-1))

This will put all words, exept the firstname, into the lastname field. If there's only one word, then it's assumed to be the last name.

Jeff

[Lee Smith]

Hi DoniDavioni

FileMaker has a tech article about this, http://www.filemaker.com/ti/105998.html

also, take a look at "Calculations and Scripts.fp5" that they included in the Template File with FM, record 8 "Extracting Title, First, Middle, Last Name, and Suffix from a Single Field".

In addition, several sites offer examples and/or variations of the technique, so I would do a search for parse, parsing an extraction at

http://www.fmfiles.com/

http://www.databasepros.com and see what turns up.

HTH

Lee

[waynemelrose]

Hi there,

I was searching for something similar to this, because I actually have a text file which is much more complex, in a field, that I would like to split into many fields..

I have managed to get the data separated into paragraphs, although now I don't know how to get each individual line out of that field..

help! thanks..

[Lee Smith]

I find that I can set a file up easier for importing by using BBEdit, or TextWrangler, before importing the data into FileMaker. TextWrangler is now Free from http://www.barebones.com/ is a strong editor, that can preform Grep Pattern Find and Replaces. If you don't have one of these editors, do your self a favor and get one of them today.

If you want to post a sample of your text file, I'll see what I can do to assist you help your Grep Find and Replaces.

Lee

[waynemelrose]

Hey there! thanks for the reply..

The situation is this..

My company are continuing to use an outdated Financial package (for the next 6 months or so) in which case, they have to first create new clients in this DB system, they systems bestway to export the info is either, tab delimited, comma, or returned, for each of its field values.

I COULD have them export it into excel, then run an applescript to extract that data and make a new client record, although I would like to try and make less steps, as in, have it in the clipboard, then go to filemaker, click a button, and have Filemaker create a new record, and dump the info into each corresponding field.

Essentially, what would get me out of trouble would be a text function that deals with text paragraph by paragraph, or similarly text groups.

ie GetParagraph(text;paragraph)

If one of the other text functions would do this, and I'm missing something, I'd love to see an example if you have one!

A simple example of my text dump would be

12345 //client number

wayne melrose company //clientname (can be more than 1 word)

55567667 //phone

34534543 //fax

wayne /// main contact

thanks in advance

[comment]

To break out an individual line (paragraph) out of text, use:

Substitute ( MiddleValues ( text ; linenumber ; 1 ) ; ¶ ; "" )

[waynemelrose]

You are the man!!

Substitute ( MiddleValues ( text ; linenumber ; 1 ) ; ¶ ; "" )

is exactly what i was looking for!

cheers