Separating 1st and Last names

[Steveoc]

I have csv files to import that put the first and last name in a single column. I know I can separate them into different columns in Excel before importing, but is there a way for FileMaker to do this?

I have looked and looked throughout the site and all the books I have, but nothing.

Thanks,

Steve

[Lee Smith]

First name

New Calculation Field

Text Result

LeftWords ( YourField ; 1 )

Last Name

New Calculation Field

Text Result

RightWords ( YourField ; 1 )

HTH

Lee

Edited August 5, 2005 by Guest

[Steveoc]

Perfect

Thank you

Where can I reference such material?

Steve

[sbg2]

Search for "text functions" in help, it should be ranked 2nd ( when you click the List Topics button.

[Lee Smith]

You can read about the different Functions in your FIleMaker Manual, or the Online Help.

The Help has some of the basic calculation in it to help show how they work. Click Here and read this review, and download the file through the link posted.

And, as sbg2 has stated, you can search this Forum and find an abundance of sample calculations. You need to understand the Advance Search and how to use the [color:blue] "Keywords" to locate what you are wanting. Of course, you can do like you did this time, and just asked. I made a file for archiving some of the calculations, so that I can refer to them when I'm looking for one.

Also, some of this has been simplified in v7, because of the new tools added.

Read the Help on how to conduct a search by going to [color:blue]Search >> Advance Search [color:brown]Keywords [color:blue] ?

HTH

Lee

[T-Square]

I'd like to point out that Lee's solution, while quick and efficient, is not the same as splitting the name at the comma. Lee's solution gets the left-most and right-most words in the field; if either part of a name is compound (has more than one word to it), you will only get a part of the name in that portion.

"Ludwig van Beethoven" would result in First name "Ludwig" and Last Name "Beethoven"...

[Steveoc]

Thanks to all for the help. I haven't used FM Pro (other than using the rudimentary databases I had already created) since version 3, so I am trying to catch up.

I have come to realize that my difficulties in finding answers is not so much in the lack of resources or the knowledge of how to access them, rather it is how to frame the questions. This should get better over time, but it points to the importance of a community such as this. I will also check out the resource Lee suggested. My sincere thanks to all of you.

T-Square--I understand the issue, but not the solution. My last name (O'Connor) wreaks enough havock with databases, so I would like to understand your solution.

Steve

[T-Square]

Steve--

Sorry I didn't get back sooner.

Basically, what I mean is you locate by position the comma in the string value. There is a Calculation function for this. Then, isolate the text to the left of that point as your last name:

Left[Field, CommaPos - 1]

and the text to the right as your first name:

Right[Field, CommaPos + 1]

This, of course, assumes that your name information is separated by a single comma, and is inverted form (a common format).

If your name is not in inverted order, you've got troubles, and I'd suggest locating the first space, and arbitrarily saying that the text to the left is the first name, and everything else is the last. This isn't always true, of course, but it's more true than not, and then you'd have to locate and fix the anomalies.

Since you mentioned Excel earlier, I'll note that Excel's ability to quickly split entries based on other characters, such as the comma, makes it an ideal way to split this sort of data up. If you're doing this as a one-off (i.e., you're not doing this on a regular basis), I would simply do the data manipulation in Excel (or even Word--I'll explain later).

I use Word when I have data in one column that needs to be split, but not others. I put the data into a table, select the column, and ask that Word replace commas with a tab (using the '^t' value for the tab). Use the Replace All option, but say NO when it asks if you want to search the rest of the document. Then, select the entire table, convert table to text, separating with tabs. Finally, select all the data and turn it BACK into a table, separating on tabs (remember you added a new tab into the first column), and you should have the last and first names in separate columns.

Whew.

HTH,

David