We help IT Professionals succeed at work.

first name and last initial in excel

Dier Eluom
Dier Eluom asked
on
11 Comments
1 Solution
104 Views
Last Modified: 2017-03-11
I have a spreadsheet with SMITH, Billy in a cell and I want to have it show S, Billy.  How would I do this?
Comment
Watch Question

View Solution Only

akb

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

Author

Commented:
Almost there.  Problem with hyphenated names still. For example;  Smith-Jones, Billy
akb

Commented:
What do you want to do with a hyphenated name?
Dier Eluom

Author

Commented:
SJ, Billy  or just J, Billy would be good.  Thanks.
Dier Eluom

Author

Commented:
Also getting D,ante, Zoe  instead of just D, Zoe - the last name is Delante, Zoe.
Dier Eluom

Author

Commented:
Sorry. I meant Last name Delante and first name Zoe.
akb

Commented:
I did have a mistake in the original formula. I then changed it to:
=+LEFT(A1,1)&","&MID(A1,FIND(",",A1)+1,99)
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dier Eluom

Author

Commented:
Excellent.  Thank you!
akb

Commented:
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))
Dier Eluom

Author

Commented:
Thank you!