Change First Name Last Name to First Name Last Initial with "."

MrKirky

New Member
Joined
Aug 23, 2014
Messages
6
I have looked everywhere, but what I really need is to change "John Smith" to "John S." on a public scheduling board. I've tried toying with multiple "first init last name" formulas, and can't quite figure this one out. Any suggestions?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Well, I spoke too soon. I'm sure this one is simple. How do I get it to stop with the "#VALUE!" errors when it refers to a cell that doesn't have a name in it YET? I went into options and unchecked the error correction for cells containing formulas that result in an error. That didn't work.
 
Upvote 0
I am new to this but this seems to ignore the blanks =IFERROR(LEFT(A1,FIND(" ",A1)+1)&".","")
 
Upvote 0
Thanks, but it returns "TRUE" everywhere that I had a name before, and it still says "#VALUE!" everywhere else.
 
Upvote 0
Thanks, but it returns "TRUE" everywhere that I had a name before, and it still says "#VALUE!" everywhere else.

This should work, and you won't need to turn off error correction.

=IF(A1="","",LEFT(A1,FIND(" ",A1)+1)&".")
 
Upvote 0
Try:
Code:
[/SIZE][SIZE=6][SIZE=1]=IF(LEN(A1)>0,LEFT(A1,FIND(" ",A1)+1)&".","")[/SIZE]
[/SIZE][SIZE=1]
 
Upvote 0
Your IF/THEN is working perfectly for all cells. Thank You! Thank You to the other contributors, as well!
 
Upvote 0
Your IF/THEN is working perfectly for all cells. Thank You! Thank You to the other contributors, as well!
Just pointing out that I have a friend whose name the suggested formula won't work correctly on. I'll just a made-up last for this example, but this is her real first name...

Mary Ann Friendly

The current formula returns....

Mary A.

instead of...

Mary Ann F.

If you want to be able to account for people with double first name, you will need a formula like this...

=IF(A1="","",LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1)&".")

Of course, that does not address people with double last names like someone I used to work with. His last name was...

Della Rossa

and I am sure there are other double last names as well... the actor Benicio Del Toro comes to mind... you current formula already handles those kind of names. So you have to decide which is more likely, double first names or double last names. Keep in mind, though, that whichever you chose, you will never be able to handle the name of someone with double first and last names, such as if my friend married the actor I mentioned above...

Mary Ann Del Toro
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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