Smart ways to do regular things

Tips to do things smarter and more efficiently

Excel Tip: Format Names from FName LName to LName, FName

with one comment


Do you work a lot in Excel. Do have to manipulate a lot of text strings or values. This tip might make life a little easier. This will help you convert a list of values from
<FirstName LastName> to <LastName, FirstName>

Inpout Output

The problem: A long list of names are available as First Name Last Name.

Input

But you need to display this as Last Name, First Name.

Requirement

Do note that the names may not always be 2 words, they may contain 3,4 or more words. (Eg: Vijay Dinanath Chauhan). The assumption here is the Last word (Chauhan, in this case) is always the Last Name (or Surname).

Input List

Formula in cell B2:

With Formula

 

=RIGHT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),LEN(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””)))))&”, “&LEFT(SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))),FIND(“~”,SUBSTITUTE(TRIM(A2),” “,”~”,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))))-1)

Drag the formula till the bottom of the list and you have an easy way to reformat your list of names.

Input Output List

Voila! Works on Names with 2 words or even more than 2.

Advertisements

Written by Neelesh Mohile

July 31, 2014 at 4:58 pm

One Response

Subscribe to comments with RSS.

  1. Hi man, do you have this example with the excel file, cause I´m working with excel in portugueses version and the functions wrinting diferente format… could help me…

    tkx

    Excel B2B

    August 15, 2014 at 5:49 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: