Q: how do i split first and last name without splitting my skull

I work on a project that converted legacy data, including combined names.

The answer is "it depends on the data".

Names that you know to be "very nicely" always formatted as "<first name><singlespace><last name>" make for easy conversion.

Nams from "less disciplined sources" can require a dozen or more different "automated routines", which does make the "automated" part of "automated routines" useless.  Worse than useless, actually, as your routine becomes a "automated corruption" routine instead.

I have no single answer because I cannot speak to the variety of different formats you may have in the names.

First, analyze *ALL* names in the list for a list of all patterns you will need to deal with.

Then test-run the conversions on COPIES of the spreadsheet instead of the original.

Good luck.

Assuming the names are formatted as "First Last":

you can use the search() function to locate the space, then use the left() and right() formulas to extract the names.

B1=SEARCH(" ",A1)

C1=LEFT(A1, B1-1)

D1=RIGHT(A1, LEN(A1)-B1)

If the format of the names is different then you can still use a similar method.  Post back if you need more help.

Wayne

zl,

Let's say your combined name is in Column A. The following expressions will parse out the first and last names with pretty good results if the source is "Robert Clancy" or "William and Mary Allison", for instance.

It's a little complicated, but I've done the hard part for you. Just copy the expressions into your expanded table. The last step is to Copy and Paste Values from the computed name columns to new columns for the static data.

You are going to add six columns after the combined name column. Here's the example:

First Space:

=FIND(" ", A)

Second Space:

=IFERROR(FIND(" ", A, B+1), "-")

Third Space:

=IFERROR(FIND(" ", A, C+1), "-")

Last Space:

=MAX(B2:D2)

Last Name:

=RIGHT(A,LEN(A)-E)

First Name:

=LEFT(A, E-1)

After entering these expressions in your first computation row, Fill Down to the bottom row of your table.

The approach here is to find all the space characters in the full name. The Last Name is assumed to begin just after the last space character.

Regards,

Jerry

Good news, that.  I assumed the worst, as is my habit (pessimists are never disappointed, you know ).

I use TextWrangler for file manipulation.  I has "column mode" for pulling "square sections" from the middle of lines with variable length.

Good luck with all you have left to do.

This solved it for me too — many thanks.

I wonder if Apple will ever find their own answer to Macros... you know, in a non-virus way...

AppleScript has been around since 1993 (I think) and isn't known for vulnerability to viruses. It works similarly to VBA macros, except that it is not "built into" Numbers the way VBA is built into Excel.  You run it from Script Editor or as a menu pick from the Scripts menu or (if placed within Automator) from the Services menu or a keyboard shortcut.

Below is a short script that reads the names in selected cells, splits them into a last name and first-middle, and places them on the system clipboard.

To use:

1. Copy-paste into Script Editor
2. Select the cells with the names you need to "split"
3. Click the run button in Script Editor
4. Click once in the first destination cell in Numbers.
5. Command-v to paste.

Just a quick copy-paste. No formulas or messing with text editors!

SG

tell application "Numbers"

  tell document 1 to tell active sheet

  tell (first table whose selection range's class is range)

  set pasteStr to ""

  repeat with aCell in selection range's cells

  set v to aCell's value

  set AppleScript's text item delimiters to " "

  set fstMidName to v's words 1 thru -2 as string

  set AppleScript's text item delimiters to ""

  set lName to v's last word

  set pasteStr to pasteStr & fstMidName & tab & lName & return

  set the clipboard to pasteStr

  end repeat

  end tell

  end tell

end tell

The result:

So Clever! Thanks for your help.