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?
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Dier Eluom

8/22/2022 - Mon
akb

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

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

What do you want to do with a hyphenated name?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dier Eluom

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

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

ASKER
Sorry. I meant Last name Delante and first name Zoe.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
akb

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
akb

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dier Eluom

ASKER
Excellent.  Thank you!
akb

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))
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dier Eluom

ASKER
Thank you!