Excel: format first/last name to “lastname.firstinitial” format

I have exported a CSV of a list of employees. In cell A1 (the name format is the complete first name, space and then the complete last name). How do I make a cell with complete last, underscore then first letter of the first name) example: A1, reads (John Smith) can cell B1 be formatted to show Smith_J)

It would be a lot easier to make two columns, Column A for the first name and Column B for the last name.

Your formula would then be =B1&"_"&LEFT(A1,1)

This formula basically grabs the last name (B1), adds an underscore &"_"&, then grabs the first character of the first name column LEFT(A1,1).

If you can’t divide the first name / last name column, your formula would be more complicated:

=MID(A1,FIND(" ",A1)+1,256)&"_"&LEFT(A1,1)

This formula gets all the characters that appear after the first space character =MID(A1,FIND(" ",A1)+1,256), adds an underscore &"_"&, then grabs the first character of the string (first initial) LEFT(A1,1)