Text Split with Excel Formula – Part II

Hi friends,

Here goes the Text Split formula Part II, which will explain how to split First Name, Middle Name and Last Name.

Of course, you can use this many other ways too, so try to understand the logic behind this.

Formulas Used:

Just like previous part, we are also going to use Left, Right & find formulas.

All the three formulas are much simple to use and the real magic is binding them together to get desired results.

LOGIC:

In the first part, we had used find to determine the location of space between two words and then we split it with left & right.

Now in this part we are going to find out further how to find out second space in the given string.

Situation:

If you have a string ‘Firstname Middlename Lastname’, in A3 how you will separate this text with formulas so that you will have firstname, middlename and lastname in separate columns.

Firstname:

Very easy just like what we did in the last part find out the place of separator and use left function.

The formula for the given example is…=LEFT(A3,FIND(” “,A3,1))

Middlename:

This is most interesting part that everybody wants. Here you need to use all the three functions together. First you find out 2^nd location of separator and using left formula you will get string ‘firstname middlename’. Then using right formula as in part one you can separate middlename. As this is quiet complicated I will explain this step by step and then together.

Step 1: Find 2^nd location of separator formula is … =FIND(” “,A3,FIND(” “,A3,1)+1)

Note: remember to add ‘1′ to last separator location to find out next, otherwise it will keep giving you same results.

Step 2: Find string ‘firstname middlename’ formula is … =LEFT(A3,FIND(” “,A3,FIND(” “,A3,1)+1))

Step 3: Use ‘Right’ function to separate middlename from string in step 2, formula is…=RIGHT(LEFT(A3,FIND(” “,A3,FIND(” “,A3,1)+1)),FIND(” “,A3,1))

Lastname:

Comparatively this is easy, and similar to what we did in last part the only difference is instead of 1^st location of separator we are going to use last location of separator.

The formula for the given example is… =RIGHT(A3,LEN(A3)-FIND(” “,A3,FIND(” “,A3,1)+1))

Conclusion:

This way you can split ‘n’ number words with any given separator. Try to find out way to use string functions differently it has enormous power.

Please add your feedback and comments below.

Excel – Format table with Zebra Stripes

This type of formatting of table is easily possible with Excel 2007, but before that it was a pain.

Let’s see how we can use conditional formatting to our advantage in this area.

‘Zebra Stripes’ is basically coloring your table’s each alternate row, this will increase the readability of the table.

Let’s see how to achieve this in step by step mode.

1. Select the entire table.
2. Goto Format – Conditional formatting
3. Select ‘Formula Is’ ption in 1st Dropdown
4. Enter Formula =Mod(Row(),2)=0
5. Select the color you want to fill every alternate row and click ok

The table you selected will get filled with White and other color you selected,
If you want it to be filled with other color than white You can also use following additional conditioning.

6. Goto Format – Conditional formatting
7. Select ‘Formula Is’ ption in 1st Dropdown
8. Enter Formula =Mod(Row(),2)=1
9. Select the color you want to fill every alternate row instead of white and click ok

You can use this same way for columns as well with Formula Column() instead of Row() above.

Hope you find this useful and use it in your daily works.

Please comment me your feedbacks and suggestions you can also mail me your excel queries.