Parsing names in a spreadsheet

[dndrich]

Neopals:

I have a spreadsheet with names and phone numbers. The names are in one row with the first name and last name together I want to separate the first name from the last name, and have them in different cells. I know there are all kinds of nice macros that come with the program. I sort of looked them over, but didn't see one that would do this. Does anybody have any ideas on how to do this?

Daniel

[amayze]

Hi Daniel,

You don't need a macro, just spreadsheet functions.

Firstly I'm making the assumption that all the names consist of just a first name and a surname - no middle initials or names - and name in the examples below is in cell A2.

Getting the first name is easy. Use the LEFT function in conjunction with the SEARCH function to find the space between the names and get everything to the left of it:

=LEFT(A2;SEARCH(" ";A2)-1)

Getting the surname is a little trickier. The RIGHT function doesn't take a start position (i.e. the result of SEARCH + 1), but a number of characters from the right. So you need to find out how long the name is first, using LEN.

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2))

Alternatively you can use the mid function:

=MID(A2;SEARCH(" ";A2)+1;LEN(A2))

If ALL the names have middle names or initials then you could use MID to find the middle name:

=MID(A2;SEARCH(" ";A2)+1;SEARCH(" ";A2;SEARCH(" ";A2)+1)-SEARCH(" ";A2)-1)

and again to find the surname:

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2;SEARCH(" ";A2)+1))

If you've got lots of space in your spreadsheet you can make the formulas more simple by breaking them up in to different cells by simply repeating the first two formulas.

Hope that helps - if you need a better explanation of how it works I'll try again!

Andy.

[amayze]

A little more playing around gives the following spreadsheet which can cope with a single middle name or initial:

[Lorinda]

Andy:

I've been following this thread, too, as I've wanted to do the same thing. I have a mix of names, so your last solution is of interest to me, but I'm not quite following; I presume that each of the three lines under "Full Name" Go in a cell following the full name (B13, B14, B15 respectively?)

Also, is there a way to "dump" the formula result so that it enters the actual text into the cell rather than the formula? (Once I have the names parsed, I'd like to delete the "full name" column, but I can't do that if it remains a formula)

[dndrich]

Wow! You are brilliant. I will try this later today. I, too, like some of the logic puzzles presented by manipulation of spreadsheet data. I'll let you know how it goes!

[Lorinda]

Andy:

You can ignore my first question; I experimented and discovered my assumption was correct.

I also figured out a way to "recombine" the first and middle names:

[sardisson]

[Lorinda]

I've figured out at least one way to provide the "text dump" I was looking for; After first saving the file as a calc file, I saved the sheet as a CSV file; then I reopened the Calc document, and inserted the CSV file as a new sheet. I did have to go through and wipe out all the columns I used for the parsing calculations, but that was easy to do.

I also figured out how to nest IF statements to deal with the remaining parsing issues I had specific to my sheet. I'm very glad for that, because I have 2695 rows of data to work with!

[GeorgeJ]

Don't know if this is the kind of "text dump" you are looking for:

Copy the cells containing the formulas to the clipboard.
From the Edit menu, choose Paste Special. In the Selection part, deactivate the Formulas option. Choose OK.

In my experience, this will paste the text that the formulas produced as pure text.

George

[Lorinda]

Thanks! I'll give it a try when I have a chance. And it's a good tip to include in the wiki article, which has yet to get written. (So much wiki to add, so little time... )

[LemonAid]

"Wikki don't lose that number"

Philip (See ... again, NeoOffice makes you smarter! )