We help IT Professionals succeed at work.

# first name and last initial in excel

on
104 Views
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

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

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

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

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

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

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

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)

Commented:
Excellent.  Thank you!

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))

Commented:
Thank you!