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®
Assuming the original text is in A1, put in B1:
=+LEFT(A1,1)&","&MID(A1,FIND(",",A1)+1,99)

Author

Commented:
Almost there.  Problem with hyphenated names still. For example;  Smith-Jones, Billy
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!

Author

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

Author

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

Author

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

Author

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

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