# Combine first letter and last word in excel

I have a spreadsheet of usernames. First and last name are in the same cell. Is there a formula to take the first letter of the first word and the whole second word?

For example; Cell A1 has Jon Smith.
Cell B1 should have JSmith

If you want the second word, the formula is:

``````=LEFT(A1)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))-IFERROR(FIND(" ",A1),LEN(A1)))
``````

It works like this:

`LEFT(A1)` pulls the first character in the cell

`MID(A1,[MESS 1],[MESS 2]` extracts the middle text starting at `[MESS 1]` and including `[MESS 2]` characters

`IFERROR(FIND(" ",A1),LEN(A1))+1` finds the character after the first space in the cell or, if there are no spaces, finds the very end of the text (this will make the `MID` function return nothing which is OK because there is no second word

`IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))` Finds the second space in the cell or, if there isn't a second space, find the end of the cell. If there's only one space, this will find the end of the second word which is what we want.

`-IFERROR(FIND(" ",A1),LEN(A1))` subtracts the first space position from the second space position. This gives us the length of the second word which is what we need for the `MID` function.

If you want the last word, the formula is:

``````=LEFT(A1) & MID(A1,IFERROR(FIND("ÿ",SUBSTITUTE(A1," ","ÿ",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))/LEN(" "))),LEN(A1))+1,LEN(A1))
``````

It works like this:

`LEFT(A1)` pulls the first character in the cell

`MID(A1,[MESS 3],[MESS 4]` extracts the middle text starting at `[MESS 3]` and including `[MESS 4]` characters

`IFERROR([MESS 5],LEN(A1))+1` finds the character after the last space in the cell. If there are no spaces, it returns the length of the cell which will make `MID` return nothing which is OK because there is no last word unless you count the first words as the last word but that's not what we want. `[MESS 5]` is a commonly used formula to find the last instance of a character in a cell. There's an explanation over at Stack Exchange.

`LEN(A1)` returns the length of the cell. Since we want the last word, we can tell `MID` to just go to the end.

Here you go :

=CONCATENATE(LEFT(A1,1), B1)

Or

=left(A1,1) & B1

• I think this is not quite right because the B1 cell should be created by the function. B1 should contain "=LEFT(A1,1)&RIGHT(A1,5);" Apr 27, 2015 at 13:12
• Use text to columns, delimit on spaces. This will break the name column in to 2 columns. This assumes you don't have a middle initial. Then use =CONCATENATE(LEFT(A1,1),B1) These other approaches remind me of "the complicators gloves"...
– skub
Apr 27, 2015 at 13:51