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