Text Split with Excel Formula – Part I

It always happens that you need to use text to column feature in excel. for some it’s rare but from some it’s everyday exercise. Think about the advantages that you will get if you can split text with formulas.

Let’s see how you can do that with example:

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

There are two formulas involved.

Left and right

How to Use left formula for fisrtname:
the main factor in this formula is finding out the string length of the firstname as it will vary. The solution is find out the place of separator (space in given example)

The formula for given example will be… =left(A3,find(” “,A3,1))

Now the difficult part of it that is the right formula use for lastname:
here as well the main factor is finding out the length of the string lastname as it will also vary everytime. so to find out the length we will first find out the place of separator (space in given example) and reduce it from the total length.

The formula for given example will be… =right(A3,len(A3)-find(” “,A3,1))

In next part we will see how can we use the formulas to split text “Firstname Middlename Lastname”.

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.

Date formula help

I have a column of dates (column G) in date format and I’d like another column to display
the fiscal or academic year for that date. I’d basically like a formula that will look at the date in column G and tell me if it is from 2001-02 or 2002-03, etc using July 1-June 30 as the fiscal or academic year. How do I do this?

Formula :

=IF(G3>DATEVALUE(“06/30/” & YEAR(G3)),”Academic Year ” & YEAR(G3) & ” – ” &
YEAR(G3)+1,”Academic Year ” & YEAR(G3)-1 & ” – ” & YEAR(G3))