Use Mid, Find & Len function to extract substring

Use Mid, Find & Len function to extract substring

Mid function requires three arguments, first the text (could be a cell reference) to look in, the start point and number of characters to return.

What to do when the start position and number of characters to return (length) is unknown?

For example we have a list of email addresses and we want to extract the domain which is after @.

Here comes the use of Find function along Mid function. The Find function, have three arguments. First one is text to find, second is within text (could be a cell reference) and third the starting point of search is optional.

Suppose in cell A2 we have an email address amiqullah@gmail.com . With Find function, we can know the position of @. So the syntax would =FIND("@",A2,1) and the result is 10. So the domain name in this mail address is starting from 11 onward.

Now let us use the Mid function to extract the domain name. Example is given below.

v  The email address is in cell A2. So first specify the text we are looking in =Mid(A2,

v  After that we need to tell the starting point for that use Find function and add 1 so if the Position of @ is 10, we want our search start from 11. FIND("@",A2,1)+1

v  Now the third argument is to specify the length of text that we want to extract. For that first pull the length of our email address len(a2) and then exclude the length of characters that are till @. For that use the same Find function. So complete syntax is LEN(A2)-FIND("@",A2,1).

v  And our complete formula is MID(A2,FIND("@",A2,1)+1,LEN(A2)-FIND("@",A2,1))

In case of any confusion, please feel free to contact me.

A	B	Formula in Cell B
amiqullah@gmail.com	gmail.com	MID(A2,FIND("@",A2,1)+1,LEN(A2)-FIND("@",A2,1))
anjum@shalimar.com	shalimar.com	MID(A3,FIND("@",A3,1)+1,LEN(A3)-FIND("@",A3,1))
nawazanjum@malikgulfaraz.com	malikgulfaraz.com	MID(A4,FIND("@",A4,1)+1,LEN(A4)-FIND("@",A4,1))
Mianamjad@quresh.com	quresh.com	MID(A5,FIND("@",A5,1)+1,LEN(A5)-FIND("@",A5,1))

Use of Excel SIGN Function

SIGN Function

The Excel sign function checks the sign of a number and gives result of 1 if the number is positive, 0 if number is zero and -1 if the number is negative.

Here we have a use of SIGN function. For example we have cost data of this year and last year and we are looking at % variance.

Please see in the below table when value is changing from -18 to -10 the normal calculation give us % change as -44%. Even though there is improvement in year over year number but the % is in negative. So we can use the SIGN function here to return the correct result each time.

A	B	C	D
Cost YE 2014	Cost YE 2015	Variance calculated with Sign function	Variance calculated by normal standard	Formula in Cell C	Formula in Cell D
-18	-10	44%	-44%	(B3-A3)/A3*SIGN(A3)	(B3-A3)/A3
10	12	20%	20%	(B4-A4)/A4*SIGN(A4)	(B4-A4)/A4
13	11	-15%	-15%	(B5-A5)/A5*SIGN(A5)	(B5-A5)/A5
-11	-10	9%	-9%	(B6-A6)/A6*SIGN(A6)	(B6-A6)/A6

Data Validation – Dynamic (Excel 2010) – From Table

Data Validation – Dynamic (Excel 2010) – From Table

Data validation is used so users enter the expected data only, available in a drop down. But with static data validation, if we want to add more values in our list, we will first have to update the source list and then update the source range in Data Validation.  

But there is a way to handle this issue. For example, in column A we have a few product’s listed which we want in another cell to appear in drop down. And as per our need when we add/modify another product in the list (Column A), it start appearing in the drop down.

1- For this, first create a table of the list, for this select the data A1:A9 and press Ctrl T.

2- The default table name is Table1, if you want to change then have your cursor somewhere in the table and go to design tab, under table name the name is appearing and can be changed.

3- Select all record of the table excluding the header (A2:A9) then go to Formulas tab > Define Name and give a name to highlighted range i.e. product

4- In the Refers to tab refer the table like in our case =Table1

5- Select Cell C1 and go to Data > Data Validation. In the Data Validation dialog box, settings tab, choose list under Allow: and in the source box write equal sign and the named range. Like in our example it would be =Product. Press Ok.

6- The drop down list is available in cell C1

7- Now important thing is that we can add a new value to our list, simple go to cell A10 and type another product and press ok or move out of cell and check in C1 cell, the new product is in the list.

Please refer to below snapshots for better idea.

Excel Fill Series

Excel Fill Series

For example, we want to create a list of dates from 01/01/2015 to 12/31/2015. We can type the date 01/01/2015 in cell A1 and then drag it down until we reach 12/31/2015. It will take some time as it’s difficult to stop the mouse at exactly the right cell.

So an option is to use the fill series option. The series dialog box has many options and some of them get automatically selected as the selected cell in our case has a date. If the value in the first cell is a number then we’ll see the ‘Date unit’ options are greyed out:

As we want to fill dates down column A, so we need columns selected.

--Type as Excel detected is Date.

--It has also assumed that we want to increase series by days, as opposed to weekdays, months or years.

--And the step value or increment value is one day at a time.

--So I need to enter the stop value which is last date of my series which in our case is 12/31/2015.

Click ok and you will have a list of dates from January 1 2015 to December 31, 2015 in a few clicks.

Note: (I learned it from http://www.myonlinetraininghub.com/)

Pictograph in Excel

Pictograph in Excel

We can paste a picture from clipboard onto a chart.

For this click on your picture and copy it, suppose it’s in MS Word so copy from there then click on graph series and paste it there (ctrl v).

See the below snap, first graph is standard and the other is with Pic.

We can do some more formatting as well. For that right click on series then Format Data series. Then go to ‘Fill’ Option. There are 3 options. Stretch is default, Stack and Stack & Scale to.

With Stack option the chart will look like below.

With Stack & scale option, we can enter input.

There are other options in the Fill tab like Gradient Fill, Picture or texture fill, which can be tried to make bars look more eye catching.