Separating First and Last Names Using Formulas

To separate the name John Smith, in cell A1, use two formulas:

The formula that extracts the first name is:

=LEFT(A1,FIND(” “,A1))

The formula that extracts the last name is:

=MID(A1,FIND(” “,A1)+1,LEN(A1))

The FIND formula returns the starting position of one text string within another text string.
Screenshot // Separating First and Last Names Using Formulas
Separating First and Last Names Using Formulas

10 thoughts on “Separating First and Last Names Using Formulas

  1. 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

  2. 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.)

  3. 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)

  4. “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”

  5. 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.,

  6. 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.

  7. “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.

  8. “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”

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × = thirty six

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>