About
Pricing
Community
Teams
Start Free Trial
Log in
Dier Eluom
asked on
3/11/2017
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 Excel
Microsoft Office
11
1
Last Comment
Dier Eluom
8/22/2022 - Mon
akb
3/11/2017
Assuming the original text is in A1, put in B1:
=+LEFT(A1,1)&","&MID(A1,FI
ND(",",A1)
+1,99)
Dier Eluom
3/11/2017
ASKER
Almost there. Problem with hyphenated names still. For example; Smith-Jones, Billy
akb
3/11/2017
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
3/11/2017
ASKER
SJ, Billy or just J, Billy would be good. Thanks.
Dier Eluom
3/11/2017
ASKER
Also getting D,ante, Zoe instead of just D, Zoe - the last name is Delante, Zoe.
Dier Eluom
3/11/2017
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
3/11/2017
I did have a mistake in the original formula. I then changed it to:
=+LEFT(A1,1)&","&MID(A1,FI
ND(",",A1)
+1,99)
ASKER CERTIFIED SOLUTION
akb
3/11/2017
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
3/11/2017
ASKER
Excellent. Thank you!
akb
3/11/2017
or
=IF(COUNT(SEARCH("-",LEFT(
A1,SEARCH(
",",A1))))
=1,LEFT(A1
,1)&MID(A1
,SEARCH("-
",LEFT(A1,
SEARCH(","
,A1)))+1,1
)&MID(A1,S
EARCH(",",
A1),99),LE
FT(A1,1)&M
ID(A1,SEAR
CH(",",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
3/11/2017
ASKER
Thank you!
=+LEFT(A1,1)&","&MID(A1,FI