Link to home
Start Free TrialLog in
Avatar of Dier Eluom
Dier Eluom

asked on

first name and last initial in excel

I have a spreadsheet with SMITH, Billy in a cell and I want to have it show S, Billy.  How would I do this?
Avatar of akb
akb
Flag of Australia image

Assuming the original text is in A1, put in B1:
=+LEFT(A1,1)&","&MID(A1,FIND(",",A1)+1,99)
Avatar of Dier Eluom
Dier Eluom

ASKER

Almost there.  Problem with hyphenated names still. For example;  Smith-Jones, Billy
What do you want to do with a hyphenated name?
SJ, Billy  or just J, Billy would be good.  Thanks.
Also getting D,ante, Zoe  instead of just D, Zoe - the last name is Delante, Zoe.
Sorry. I meant Last name Delante and first name Zoe.
I did have a mistake in the original formula. I then changed it to:
=+LEFT(A1,1)&","&MID(A1,FIND(",",A1)+1,99)
ASKER CERTIFIED SOLUTION
Avatar of akb
akb
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent.  Thank you!
or
=IF(COUNT(SEARCH("-",LEFT(A1,SEARCH(",",A1))))=1,LEFT(A1,1)&MID(A1,SEARCH("-",LEFT(A1,SEARCH(",",A1)))+1,1)&MID(A1,SEARCH(",",A1),99),LEFT(A1,1)&MID(A1,SEARCH(",",A1),99))
Thank you!