Open Site Navigation
Maveristic

Be Valuable, Not Available

© 2020-2021 by Maveristic 

Web Design & Development Company

Subscribe to Our Mailing List

Thanks for submitting!

Wix Icon Level Partner
View Our Ratings
Maveristic

Be Valuable, Not Available

Follow Us

  • LinkedIn
  • Facebook
  • YouTube
  • Instagram

Email Us:

support@maveristic.in

Contact Us:

+91-9821610806

  • Canute Fernandes

Split names in Excel

How comfortable are you with Excel?

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

I will share a function that 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 the 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 using 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 a literal sense this is a 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 command the Excel to start from the left and find the first blank space (” “) which will be the last character and 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 to 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 until the first blank space, now replaced with a “#” 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, 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!