Converting full name into first intial & full last name

B

brutusmc99

Board Regular
Joined
Oct 6, 2014
Messages
111
I found a resource showing how to convert a full name into 2 initials (e.g., John Smith into JS), but I'm curious if anyone knows if it's possible to convert a full name into the first initial (with a period) & full last name (e.g., John Smith into J. Smith).

thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
post your code, JS i will utilise finding a space between names, so once you have achieved the first length, you would use LEN to slice that and carry on
 
Upvote 0
If there is no middle name or initial try

PHP: 
=SUBSTITUTE(TRIM(LEFT(A1)&"."&". "&RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"..",".")
 
Upvote 0
or
Code: 
=LEFT(A1)&"."&MID(A1,FIND(" ",A1,1),99)
 
Upvote 0
Hi,

If there is no middle name then you can use below formula.

=LEFT(J12,1)&"."&" "&RIGHT(J12,LEN(J12)-FIND(" ",J12))

Hope this helps.

Regards,
Amitkumar J.
 
Upvote 0
Thanks for your reply. Unfortunately I can't seem to find the site where I got the code before. Fortunately, all the other formulas given to me in this thread work well, so I'm good to go.
 
Upvote 0
mole999 said:
post your code, JS i will utilise finding a space between names, so once you have achieved the first length, you would use LEN to slice that and carry on
Click to expand...


Thanks for your reply. Unfortunately I can't seem to find the site where I got the code before. Fortunately, all the other formulas given to me in this thread work well, so I'm good to go.
 
Upvote 0
MARK858 said:
If there is no middle name or initial try

PHP: 
=SUBSTITUTE(TRIM(LEFT(A1)&"."&". "&RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),"..",".")
Click to expand...


Works good, except I have one odd name "Luc Richard Mbah a Moute," which I hope to convert to "L. Richard Mbah a Moute," but got "L. Moute." But for all other names, included hyphenated names, it works great.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Take full name from one worksheet, make appear as first name last initial in another
Replies
3
Views
184
awoohaw
awoohaw
Y
  • Question
Need Formula Help Please
Replies
8
Views
245
dreid1011
dreid1011
B
  • Question
VBA to show and change list of names depending on selection
Replies
7
Views
453
mumps
mumps
Mel Smith
  • Solved
Make all listed names display in only one way - initials and last name
Replies
5
Views
128
Peter_SSs
Peter_SSs
K
  • Solved
Change "Last name, first initial" to first name last name in sentences
Replies
2
Views
759
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,583
Messages
6,120,383
Members
448,956
Latest member
JPav

Share this page

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