Split names in Excel

How comfortable are you with Excel?

Do you struggle with the basics or you manage to scrape through…!

I am going to share a function which will help you with your data analysis, a small function but a powerful one…

We often download reports and start working on them, now the catch here is the most required or necessary column is the “Name”, right?

I am sure you agree… But not every system will give you the desired output, unless the report had been developed as per your primary requirement… Even if it were developed many-a-times due to change in process the output is not in the desired format….

We will check how can we split the name with a formula… You must have been use a Tex-to-column function or doing it manually if there were few records…

Before we see the function of splitting the name, I will share with you a few formatting shortcuts:

  1. Proper: Sentence case

  2. Upper: All Caps

  3. Lower: All Small

  4. Alt+H+FS: Font Size

  5. Alt+H+FF: Font Style

  6. Alt+H,AT/AM/AB: Top Align | Middle Align | Bottom Align

  7. Alt+H,AL/AC/AR: Left | Center | Right

  8. Alt+H+B: Border Setting

  9. Alt+H+H: Fill Color

  10. Alt+H+FC: Font Color

  11. Alt+O,C,A: AutoFit Column Width

  12. Alt+O,H,R: Rename Worksheet

The above will surely help you save a couple of mins to tidy up your work.

Now, let’s see the Split Function. No, don’t look up for a split function in literal sense this is combination of formulas…

Left, Right, and Substitute

I am wanting to split my name i.e. Canute Vernon Fernandes.

First Name: =LEFT(A3,FIND(” “,A3,1)-1)

Output: Canut“e”

Explanation:

We are commanding the Excel to start from the left and find the first blank space (” “) which will be the last character and to subtract the blank space by inserting -1.

**If you do not insert -1 it will include the space after “e” and give an output of 7 characters.

Last Name: =RIGHT(A3,LEN(A3)-FIND(“#”,SUBSTITUTE(A3,” “,”#”,LEN(A3)-LEN(SUBSTITUTE(A3,” “,””)))))

Output: Fernandes

Explanation:

Using the combination of Right and Substitute we will be able to get the Last name. There are 3 parts to this formula:

  1. RIGHT(A3,LEN(A3)-FIND(“#”: Here, the excel will come the right of the cell and identify the “#” character in the string.

  2. SUBSTITUTE(A3,” “,”#”: Here, the first blank space form right will be replaced with “#”.

  3. LEN(A3)-LEN(SUBSTITUTE(A3,” “,””))))): Here, it will give the output which will be considered till the first blank space, now replaced with a “#” that will be subtracted from the output.

Middle Name: =LEFT(RIGHT(A3,LEN(A3)-FIND(” “,A3,1)),FIND(” “,RIGHT(A3,LEN(A3)-FIND(” “,A3,1)),1))

Output: Vernon

Explanation:

We will use the Left & Right function to arrive at the text that is in the middle of the name. This function has 2 parts to it.

  1. LEFT(RIGHT(A3,LEN(A3)-FIND(” “,A3,1)): Here, the excel will start the command from the left but will consider only the data to the right of the first blank space from the left.

  2. FIND(” “,RIGHT(A3,LEN(A3)-FIND(” “,A3,1)),1)): Here, the excel will start the command from the right but will consider only the data to the left of the first blank space from the right.

Try this by yourself!

Please comment the function which you would want to learn and explore… I shall get the same ready for your perusal…

#excel #splitthename #texttocolumn #formulatosplitname #howtosplitnames

#excelsplitthenametexttocolumnformulatosplitnamehowtosplitnames

0 views0 comments

Recent Posts

See All

How to integrate Razorpay Payment Gateway in WIX?

Finally, Razorpay has been fully integrated with WIX. Now you can add not only PayU but also Razorpay to your WIX website. You can now use both most recommended payment gateway providers in India on y

Call Now

9821610806

Maveristic

Be Valuable, Not Available

Menu

Home

About

Projects

Blog

Contact

Tel: 9821610806

Email: canute@maveristic.in

India

Follow Us

  • YouTube
  • Facebook
  • Twitter
  • LinkedIn
  • Instagram