To separate the first and the last name from a cell in Microsoft Excel, you can use a combination of a few functions. Use the “Left” function combined with “Search” function for picking the first name and forthe last name use the “Right” function combined with “Len” function and “Search” function.
To separating names follow below steps in Excel:-
Example : We have a list of names in Column “A” and we need to pick the First name from the list. We write the “LEFT” function along with the “SEARCH” function.
To pick the Last name from the list, we need to use the “RIGHT” function along with “SEARCH” function and “LEN” function
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Homero's problem wasn't in naming the cells, forgetting '=' or something like that... In spanish, you simply have to translate the Spanish formulas or else it doesn't work. In this case, LEFT=IZQUIERDA, FIND=ENCONTRAR, LEN=LARGO. I didn't find what's MID (I tried MEDIANA but it didn't work). Instead, I changed the formula and used RIGHT (DERECHA) and it really fine.
As to John Foster's command... Text to columns is good when the formatting of all the names is fine or when you don't really use the info in that spreadsheet. But in the case you want cells C10 and C11, for instance to represent the first and last name of what's in A1 (useful in some forms...), the way of the formula is the best...
For example, starting with this little formula, I adapted it to split names within 4 columns (first, middle and 2 last names (as used here in Mexico). The formula removes comas, present in some of my records, etc... Text to columns just can't do that!
Those are my 2 cents. I hope that helps.
Michaël Niessen"
"Excel has a built in function under the Data menu called ""Text to Columns"" which can be used to aplit a list of names into 2 columns. One contaisn the first name, one contains the last name.
Simple."
Did you enter the correct name in cell A1? the formula =LEFT(A1,FIND(" ",A1)) and =MID(A1,FIND(" ",A1)+1,LEN(A1)) will look for the value at cell A1. If you typed the full name in another cell, edit the formula and refer to that particular cell. Hope this solves the problem.
How about the name is: John III Smith Jr.
The formular works great. I have excel 2002 and it worked without error.Check you typing before hitting the enter key.
i lika this tippos verry mucho. it his grandissimo. it maka my lifo so peesesy i like so mcuh. dank u. u need ur laaan mode jus axe me ok.,
"Homero,
The ""nombre""-Error means that EXCEL did not recognoze the functions ""LEFT"",""MID"",""LEN"" ans ""FIND"". Find the Spanish equivalents and use those instead of the English ones.
GoodLuck, Jean-Pierre"
I dont know what I have wrong, but on the first celd ( who show the name "jhon") did not show the name, show this : #¿NOMBRE? and on the second ( who show the last name "Smith")did not show Smith, only show this:=MID(A1,FIND(" ",A1)+1,LEN(A1)). you see I copy all the correct formula, i dont forgot any line. please if you can helpme, I have Excel on Spanish version ( excel 2002)
works great! (Homero, I'm not sure about the first line problem, but on the second line, you forgot to paste the "=" before the formula.)
in the tip"Separating first name and last name using formulas in Microsoft Excel" I have excel in spanish, and I put the formulas but the firs showme"#?NOMBRE?(THIS MINE "NAME"), and the second showme only the FORMULA, that I paste