If you have someone’s name in a cell and want to automatically extract initials, you can use LEFT, MID and FIND.
As always when working with text cells, you can wrap with UPPER, LOWER or PROPER.
A | B | < B function > | |
---|---|---|---|
1 | Joe Bloggs | JB | =LEFT(A1)&MID(A1,FIND(” “,A1)+1,1) |
2 | joe bloggs | JB | =UPPER(LEFT(A2)&MID(A2,FIND(” “,A2)+1,1)) |
I also found online a funtion to manage three names – seems long so I imagine there is an easier way to do it, but it works:
A | B | < B function > | |
---|---|---|---|
1 | Joe John Bloggs | JJB | =IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=0,LEFT(A1,1),IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=1,LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1),LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1)&MID(A1,FIND(” “,A1,FIND(” “,A1)+1)+1,1))) |
Leave a Reply