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”.

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))

Quick list in place

Many a times you have a situation where in you have to type in again the same thing you have typed some time before. What you do? go select that cell again and copy paste….

Here is a way out to quick select list, just use (Alt + down arrow) and the entire list will appear just like auto filter in place. Then you can select whatever option you want.

More short cuts

Quick sheet add (Shift + F11)
Quick chart add (Select source data and press F11)
Quick copy and edit value of the cell in cell below (Ctrl + Shift + ‘)
Quick copy and edit formula of the cell in cell below (Ctrl + ‘)
Edit or create a comment in the current cell (Shift + F2)

Shortcut Keys for Formulas

‘Ctrl + A’
Use this key immediately after typing the function name e.g. =Sum, It will open up the function argument window, which provides details about input parameters of that function.

‘Ctrl+shift+A’
Use this key after typing the function name and ‘(‘ e.g. =Sum(, It shows the syntax for the function in-place, this proves useful many times.

How to create super hidden secrete worksheets

Many of the times there are situations where you wanted to have one confidential worksheet where you can keep your secrete data and which you refer on other sheets, or may be only some hidden worksheet whereby only specific person knows about it. And you wonder how to do that in Excel.

Yes there is a way in Excel, no I am not talking about sheet ->hide, all knows about it. It hides sheets from appearing in the tabs but when user goes and select Unhide Sheets from format menu it shows the list of all hidden sheets so a big problem!!!!. There is one better way to achieve more than just hide. And that’s the visibility of the sheet. Now in Excel there are 3 modes associated with sheet’s visible property as follows:

1. xlSheetVisible (-1)

2. xlSheetHidden (0)

3. xlSheetVeryHidden(2)

You can see and change this using VBA Editor (Alt + F11), Select sheet in Project Explorer (Ctrl + R), and see last property in properties window (F4).

So when you select ‘Sheet Hide’ the visible property to ‘0’ i.e. ‘xlSheetHidden’, which also appears in the list of hidden sheets.

To make sheets that will not appear in hidden sheets list, we need to change this property to xlSheetVeryHidden(2), so once you through with your work on that sheet you can come in VBA Editor and create this way super hidden secrete worksheet.

But wait that’s not all you can even use references from this sheet on other hidden sheets, as the sheet is still present in workbook all calculations will properly work.

But yes you still have to protect your workbook for the purpose of security.