first name and last initial in excel

Dier Eluom
Dier Eluom used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
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:
Try this:
=IF(COUNT(SEARCH("-",LEFT(A1,SEARCH(",",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:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial