0

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

2 Answers 2

2

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.

1

Here you go :

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

Or

=left(A1,1) & B1

2
  • 1
    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);"
    – Valinho
    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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .