Hi Everyone I cannot seem to get this right. I have a cell with text such as
DOE,JOHN A
I need it to spit out either
John Doe
or I can have two columns, one for last name
Doe
and one column for first name
John
If someone can show me how to do both of these that would be amazing!!!!
In recap I need to separate both the first and last name, delete the middle initial at the end and have it in "proper" case. thanks a million to anyone who can solve this mystery!
Last edited by jeffreybrown; 06-21-2012 at 08:17 PM.
Excel 2007
data => text to column
It is not smart crossposting.
http://www.excelforum.com/excel-gene...nd-spaces.html
Trying to delete the other post.
However is their a formula to use rather than text to columns as I need it to fill other titled columns in my spreadsheet instead of replacing the entire contents as some people have 2 middle names and it spills over into my other columns in the spreadsheet, so a formula would be cleaner in that sense
If you really want a formula to do it, you can do this (it will take 3 extra columns):
In one column type the formula =Proper(RIGHT(A1,LEN(A1)-FIND(",",A1)))
In another the formula is =Proper(LEFT(A1,LEN(A1)-(LEN(A1)-FIND(",",A1)+1)))
And finally, the last column =Proper(LEFT(B1,LEN(B1)-(LEN(B1)-FIND(" ",B1)+1)))
Assuming you can change the references
Last edited by mshale; 06-21-2012 at 04:24 PM.
I need it to do the comma first then the space after for instance
de leon,john m
doe,john m
I need it to be
John De Leon
John Doe
text to columns gives me 4 columns for John De Leon and 3 for John Doe, when all I need it to be is 2 columns
If you try my suggestion, you can just hide the column that still has the initial in it.
Just tried, thought we had it however it gives me "#Value" for people who dont have a middle name for instance
Doe,John M works
De Leon,John works
Doe,John doesnt work so I would have to unhide the first row and copy it over, but thats tough for 100's of names... I guess there is no quick fix for this
nah, you can fix that with an iferror statement in the last formula.
=IFERROR(PROPER(LEFT(B1,LEN(B1)-(LEN(B1)-FIND(" ",B1)+1))),B1)
Boom! worked! Thanks!!!!!!!!!!!
No Problem.
Click that little star below my name
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks