excel data question

[Agtuary]

I have a list of names in excel, the entire name is in one cell. Some have middle initials, some don't. I would like to know an easy way to split the names into two cells, one with first name and middle initital, the other with just last name. For example

John Q. Public --> John Q. | Public
Jane Public --> Jane | Public

Another option is to grab the last name only and put it in a new cell, keeping the original cell intact.

[Dr T Non-Fan]

Is that a list of passing candidates' names? Just wondering. Some of tose folks have three or four middle initials.

I don't think there's an easy way to solve this. You might have to go back to the source text extract and have tabs or quotes/commas put in the appropriate places.

[Elisha]

Saucy,
Try copying the original column -> On the new (copy) column, do data -> Text to columns -> delimited -> space. Also, trying using a period. There are the LEFT(cell, #) and RIGHT(cell, #) options as well.

Oh, you still haven't given me that vital stats I asked for before (Name, Bday, etc.)

[Agtuary]

No periods in the name, that was my addition. Left, right & mid don't work because names have different #s of letters. Text to columns is the best way right now, unfortunately it gives me two columns with last names in it (i.e. first column has first names, second column has some last names & some middle initials, last column has blanks & last names) and still requires some work.

What I was hoping for was a function or group of functions that would read the cell from right to left, find the location of the last space and pick all characters to the right of the last space. I guess I will just have to go through it the hard way.

[Agtuary]

oh yeah - Avery Lynn, 8lbs 8oz, 5/22/02

[Intents]

a nasty nested if testing for spaces is the only way seeing it from functional point of view

an alternative way would be to save the file (or just pick of names for this operation) and save as text

then close the file

then open the file as text, using space delimiters

then sort the file on expected last names

this will bring blanks to beginning or end and the missing middle names, which are really last names, can be moved to expected place

a final sort will put you back to your original situation

(carry some number along to make this step easier)

[Anonymous]

What I was hoping for was a function or group of functions that would read the cell from right to left, find the location of the last space and pick all characters to the right of the last space.

If you are willing and able to write some VBA code, this can be accomplished very quickly as follows. Here's the algorithm to accomplish this.

Loop through desired cell range
L = len(cell)
for i=L to 1
if mid$(cellvalue,i,1) = " " then
last name = right$(cellvalue,L-i)
first name = left$(cellvalue,i-1)
endif
next i
End Loop

(sorry for lack of readability - editor doesn't seem to allow proper spacing)

[whisper]

for i=L to 1

You are stepping down in this bit of code. Don't you need to have a step statement in it?

i.e. -
for i = L to 1 step -1

[Pi Man]

*in Excel, open the VB window for the workbook (alt-F11) and insert the funtion below into a new module for your workbook.
* paste the names into column A
* enter the following into column b: =lastname(a1)
TADA! It's not the most beautiful thing in the world, but it works great. If your name has no spaces, function returns "XXX"; you can change to suit your own tastes...

Function lastname(sFullName)
'initialize s=string; i=integer
sBlank = "XXX" 'custom text here for name with no spaces
sChar = sBlank
iLength = Len(sFullName) 'name length
iDecr = iLength

'loop backwards through name until space found
Do Until sChar <> sBlank Or iDecr = 0
sTest = Mid(sFullName, iDecr, 1)
If sTest = " " Then sChar = Mid(sFullName, iDecr + 1, 99)
iDecr = iDecr - 1
Loop

'result
lastname = sChar

End Function

[Patience]

I believe it was said, but you would set up a formula using the cell length and the character posiition of the blanks. Then a right command & left command to split the cell, remember the two pieces will be one less then the total length to eliminate the separating blank

Problems will occur when you have suffixes (Jr., III etc.)
or possibly two part names (Mc, O', De) if an extra space is used. These would have to be searched for a fixed manually.

[Ben Kenobi]

If you enclose your code in tags, it'll go to a monospace font and preserve indenting.

[urysohn]

It ain't pretty, but here's a formula you can cut and paste. Obviously, it assumes the full name is in cell A1:

=RIGHT(SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),LEN(SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-FIND("$",SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

(And Congratulations!)

[Agtuary]

Thanks for all of the replys and sugestions.

Patience, I'm not sure I understand how the left & right functions would work, considering the middle initial. Is there a function that will return all the locations of a specific character?

Unfortunately I am VB illiterate, I am slowly learning but it is usually unnecessary for most of my work. I will probably try the code at some point in the future.

[Agtuary]

Thanks Urysohn, I was in the middle of my post when you posted yours.

[Pi Man]

the FIND and SEARCH functions return the FIRST okurence of the charackter you are looking for. Your problem is with names like "Bob X Smyth". The functions above will work on this name because they start looking from the RIGHT, not the LEFT. the problem remaining (thanks, Patience!) is with names like "John Q Public III" and "Alex P Keaton Jr"; the functions will return "III" and "Jr." Depending on the volume of data, you could browse or sort the results OR you could scrub the data (do a search/replace on II, III, Jr, and Sr)... it might help to replace " II" with ""...

don't worry about no/little experience; there's a bunch of folks out there (myslef included) that could be described as 'not the sharpest knife' or 'not the brightest bulb'... but we keep pluggung away! :D

[Dr T Non-Fan]

"I would like to know an easy way..."
As you can see, there is no easy way.

If it's more than 1000 people, you've got some manual labor ahead of you.
If it's only 50 (I did this for the 8V pass list I posted), then it's only 30 minutes of work.

[urysohn]

30 minutes for 50 items? You could manually go through and retype them quicker than that. Even if you just parse the text based on a space delimiter, you could do it in 5 minutes (quickly aligning your columns and pulling out the "Jr", "III", etc.). 1000 items is a different story.

I would still use the formula I posted, but then have a second formula to the right of that one. That second formula could as a True/False (or 0/1) question: Is the word to my left equal to an invalid name? Invalid names could be placed in a separate table off to the side. Example:
T1:T20 contains my list of invalid names - things like "Jr" or "III".
C2 contains the first of the formulas picking off the last name
In D2, I put:
=IF(VLOOKUP(C2,$T$1:$T$20,1)=C2,0,1)
All the "1" values are where my "last name" was one of the invalid choices. For those (which shouldn't be many), I manually replace the "Jr" with the appropriate last name. As I replace the names, the 1's change to 0's. Have a SUM at the top of the 0/1 column - the sum should go to 0 once all the names have been corrected. Of course, you'd still want to do a quick scan through the list to make sure you got all the "invalid" choices in your table.

[Anonymous]

Whisper, You are correct that I overlooked the step of course, but the code snippet was provided as an algorithm only. I did not really expect it to be correct.

Ben Kenobi, thanks for the tip. I knew there must be some reason that I kept you around after you messed up with that Anakin kid.

Regards

[Dr T Non-Fan]

ury, you overestimate my tpying abilities.