Change First Name Last Name (First Last) to First Name Last Initial ( First L.)

kznmrexcel

Board Regular
Joined
Jun 16, 2010
Messages
86
Office Version
  1. 2016
Platform
  1. MacOS
Hi, everyone,

Is there a way to take a full name and convert it to a first name last initial without adding an additional column/step with "text to columns?"
I would like to convert from cell D2 , < John Doe > to cell F2 < John D. >
I appreciate any help with this.

Thanks,
Karen
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi KZNadalin,

Straightforward if they're all the correct format but I'd suggest checking your data for anomalies.

Book2
DE
1NameResult
2John DoeJohn D.
3Sarah JonesSarah J.
4Rip van WinkelRip v.
5Jorge Da SilvaJorge D.
6Mr. J. SmithMr. J.
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=LEFT(D2,SEARCH(" ",D2)+1)&"."
 
Upvote 0
If there is any danger that D may contain a leading or extra spaced then use the formula with TRIM
If you need the Period ??? then use like F4
Book1
DEF
2John DoeJohn D
3Frederick SmithFrederick S
4Frederick SmithFrederick S.
Sheet1
Cell Formulas
RangeFormula
F2F2=LEFT(TRIM(D2), FIND(" ",TRIM(D2),1)+1)
F3F3=LEFT(D3, FIND(" ",D3,1)+1)
F4F4=LEFT(D4, FIND(" ",D4,1)+1)&"."
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top