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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

MrKirky

New Member
Joined
Aug 23, 2014
Messages
6
Wow. Simplicity even. I "overthunk it until my head hurt." That worked. Thank you! :)
 

MrKirky

New Member
Joined
Aug 23, 2014
Messages
6
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.
 

DeezNuts

Board Regular
Joined
Aug 12, 2014
Messages
177

ADVERTISEMENT

I am new to this but this seems to ignore the blanks =IFERROR(LEFT(A1,FIND(" ",A1)+1)&".","")
 

MrKirky

New Member
Joined
Aug 23, 2014
Messages
6
Thanks, but it returns "TRUE" everywhere that I had a name before, and it still says "#VALUE!" everywhere else.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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)&".")
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
Try:
Code:
[/SIZE][SIZE=6][SIZE=1]=IF(LEN(A1)>0,LEFT(A1,FIND(" ",A1)+1)&".","")[/SIZE]
[/SIZE][SIZE=1]
 

MrKirky

New Member
Joined
Aug 23, 2014
Messages
6
Your IF/THEN is working perfectly for all cells. Thank You! Thank You to the other contributors, as well!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,155
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,444
Messages
5,675,895
Members
419,591
Latest member
mersanko

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
Top