Extract File Names from Path

Extract File Names from path :-  

Suppose we have a file path " C:\documents\personal\myfiles.xls ", and we have to extract only file name from it. i.e "myfiles.xls".

See below print shot for a reference :

Use formula :- =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Explanation :
LEN i.e length function used first to count the total length of path i.e LEN(A1) and then we subtract the total length of path without slash "\" (and without slash would be obtained by substituting it with blank with the help of SUBSTITUTE function.) i.e LEN(SUBSTITUTE(A1,"\","")) . By doing this we get to know total number of slash "\" in a path i.e LEN(A1)-LEN(SUBSTITUTE(A1,"\","")).

Once we get total number of slash  "\" in it, we distinguish the last slash with star "*", as only after the last slash "\" our required data exist i.e file name and for this SUBSTITUTE function is helpful i.e SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) . Note, LEN(A1)-LEN(SUBSTITUTE(A1,"\","")) as we found it before placed it in [instance_num] of substitute function, works as pointing the last slash "\".

Then need to find the position of our placed star "*" in a path, and for it FIND function will be helpful. Since we are interested to find the data after the star "*" for that subtract the find function result with the total length, it gives the total length of the file name i.e LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))) .

And use RIGHT function for the words from the right equal to the total length of file name that are found.
i.e =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Thank you!

Find keywords within statement.

In previous blog, we found number of words from keyword list in a statement. Now, we are interested to know specifically which words are actually found from keywords list . For Column B formula refer previous blog.

{=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

It's an array formula, Enter this formula in C2 cell and press ctrl+shift+enter instead just enter, then drag down and  right to other cells.

Note:

text :  =SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,".",""),",","")     'Defined name to this formula as "text" in our example

keywords : =Sheet2!$A$1:$A$12  

morning

people

fabric

nobody

cabinet

sauce

cynical

time

temper

curious

difficult

beneath

'Below keywords are in Sheet2 , defined name to this formula as "keyword" in our example 

Briefly understanding formula :
i.e {=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

In our example, our maximum word search from a keyword list is 3, so the column should be 3. Column for word1, word2 and word3.

First we need to know , If the columns is less and equal to the number of keywords found, then we will continue to fill the each column cell with a word that are found in a statement from a keyword list, else column will be blank. So we started formula from " {IF(COLUMNS($C2:C2)<=$B2,[value_if_true], "")}" to look if the number of columns is less and equal to the number of keywords in a statement.

Then, if we found it TRUE, the we will look for the words that are found . For that use INDEX function.

Syntax : INDEX(array, row_num , [column_num])

Here, array is "keyword" as we have to look a word from the keyword list . Further we need to find row_num.

For the row_num : We need to find the row_num of the found words in a keyword list.

We use SEARCH function to find the position of keyword in a statement . SEARCH function formula , detail explanation is on previous blog. Since, SEARCH is useful to find the position , so we used ISNUMBER function to know which words position are found and ISNUMBER brings it as TRUE if it found the position and FALSE if not. Now, we are interested to know the ROW number for TRUE values of ISNUMBER function. Use IF function and ROW function ([value_if_true]).

{................. IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)...........}

Now, As we said before number of keywords found = total number of columns.

Use SMALL function with COLUMN function, as lets say if column function result is 1, then small function will look the smallest row number of the found words from keyword list. If column function result is 2, then small function will look the second small row number of the found words from keyword list and so on.

 {.....SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))...}

So here , our formula completes :

{=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

It's an array formula, Enter this formula in C2 cell and press ctrl+shift+enter instead just enter, then drag down and  right to other cells.

Thank you !

Number of keywords uniquely present on a particular statement

From the list of keywords, we will find the number of keywords uniquely present on a particular statement.

For example :- We have keywords like "people" , "morning", etc.

In a statement, "He gave people by that morning, O Deep Thought is it?" :- Two keywords i.e "morning" and "people" are present , so an answer should be 2.

Let's see Excel formula to this issue :

In sheet 2, I have some list of keywords :

morning

people

fabric

nobody

cabinet

sauce

cynical

time

temper

curious

difficult

beneath

In Sheet 1 , I have list of statements, refer below print shot :-

Formula, in column B, simple and easy one :-

=SUMPRODUCT(--ISNUMBER(FIND(keyword,A2)))

But, above formula consists few problems, as observed in a statement  "little difficulty ruffles his temper". 

Problem : In listed keyword it is "difficult" ,  but above formula also takes "difficulty" as a keyword , which we do not need.

So to rectify the above formula , in column C we used :

=SUMPRODUCT(--ISNUMBER(FIND(" "&TRIM(keyword)&" ",A2)))

Our motive is to define a word to its limit only with no other alphabet(s) are attached to it. For that, we placed a space before and after the word.  We used the TRIM function and concatenate the one space before & after the word . As adding space imples that a word is attached with a space, no other alphabets(s). But with this formula too, our problem is not actually solved as in the same statement "little difficulty ruffles his temper" , "difficulty" word problem is solved but a "temper" word is in a keyword list but unfortunately it is not taking it as a keyword because in a statement after "temper" word there is no space and with formula above we did modify the keywords with space. Therefore, it is not considering it as similar in a keyword list. Same problem lies with this formula in a statement "He gave people by that morning, O Deep Thought is it?" , "morning" is in a keyword list but because in a statement it is "morning ,(comma)" instead of "morning (space)" our formula is considering not similar as in keyword list. Same case seen in a statement "wanted that we picked beneath cabinet stuck in a curious" with "curious" keyword , it is in a keyword list but it is not considering it as keyword because of not having a space after it in statement.

To rectify above problems we used formula in column D :

=SUMPRODUCT(--ISNUMBER(FIND(" "&TRIM(keyword)&" "," "&text&" ")))

We required to match the word(s) having comma and full stop and having no space before and after it (Mainly, first and last word(s) have no space before and after it ,respectively) with modified keyword .

For that we first used the SUBSTITUTE function (to remove comma and full stop) and concatenate it with a space before and after the statement (by adding the space in a statement , it actually add space before the first and after the last words).

To remove fullstop(.) and comma(,) use formula

=SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,".",""),",","")

and we defined it as a name "text" for the use of column D formula

For a reference please consider below print shot, "Name Manager" shows that there is a formula named as text;

So, by a formula in Column D, we succeed getting our desired result except one, that the FIND function is case-sensitive. Sometimes we do not have a proper case word in a statement.

Like in a statement "STUFFED WITH TOMATO SAUCE Even" . FIND function did not consider "sauce" keyword as a keyword because in a statement it is in uppercase case and in a keyword list it is in lowercase. Use SEARCH function instead of FIND function if you do not required case-sensitive outcome. 

Column E used the following SEARCH function :

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")))

Extract First , Middle and Last Name

Formula for First Name : -
=LEFT(A2,FIND(" ",A2)-1)

Formula for Middle Name :-
=MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))

Formula for Last Name :-
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Understanding :-
First Name : First Name starts from the first letter and end till the first blank. So, our first purpose is to find the first blank, which is done from FIND function. FIND function helps you to find the first blank , in example "B. Kumar Bansal", FIND function gives you the result 3 as the first blank is in position 3. Therefore, just before first blank, we have our required word i.e First Name, So,if we subtract 1 from FIND function result (as we have to remove the blank which takes 1 word), we get the total length of first word (i.e 3-1=2). Use LEFT function , as it looks the data from left (starting) of A2 i.e B. Kumar Bansal till the length reached before the first blank i.e 2 (=3-1) .

Middle Name : Middle Name starts after the first blank and end before the last blank. So, we need to find the first blank and the last blank. First blank will be find as like we did in First Names , but since it starts after the blank so we add 1 to it i.e FIND(" ",A2)+1 .Notice that after the first blank it can also be Last name as Middle name not necessarily exists. More than one blank means middle name exists. Before the procedure, we first need to count the difference of length with or without blank, as the difference will be the number of blanks. Total length with blank obviously can be found from LEN function and total length without blank can be found by converting the data without blank ( from SUBSTITUTE function ) and then count the length.

Total No. of  blanks in data :-

LEN(A2)-LEN(SUBSTITUTE(A2," ","")

Now, we need to find the last blank position, for that use FIND function as we know the problem that FIND function brings the position of first blank. To solve it we need to SUBSTITUTE the last blank with let say "*", and find the position of  "*" which is in place of last blank . To substitute the last blank with "*" use

SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

as, LEN(A2)-LEN(SUBSTITUTE(A2," ","") is the total number of blanks which can be use as [instance_num] to place "*" in the last blank .


Now, use FIND function to find the "*" .

FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Above formula brings the total number of words till the last blanks (i.e the combine length of first and middle name).

Since we only need to find the MIDDLE names, we have to subtract the length of first names also. For that, use
FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))
It brings the total length of middle name.

Now, use MID function to extract the middle names ,syntax:
=MID (text , start_num, num_chars)

which is,
=MID(A2,FIND(" ",A2)+1,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2))

Last Names :
Last name starts after the last blank.

To find the position of last blank as we did before in Middle name
FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

As above formula brings the total length till last blank , means the length of all except the last name.
So, if we subtract the total length with above we get the length of last name
i.e LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Lastly, use RIGHT function, syntax:
RIGHT(text, [num_chars])

i.e
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

Comparison Histogram

Comparison Histogram :

Steps on Excel : Below is an Example.

Convert Family B data into negative. And select A1:C6.

From insert tab, select bar graph (Clustered Bar) :

By selecting the Clustered Bar Graph, below is the print shot how it comes:

From Format data Series Dialog Box, and in series option bring it to 100% overlapped.

Format Major Gridlines with 'No line'.

From Format Axis dialogue box, select Major tick type as none and Axis labels as Low:

From Format Axis dialog box, and in number option select Custom and enter 0;0;0 click Add and then close. It is useful to bring the axis as positive values :

Legends deleted and from text box we text Family A and Family B to better presentation.

Thank you !

Angular or Pie Diagram

Pie Diagram - It is a sub-divided circle diagram . The comparison of the pie diagrams is to be made on the basis of the areas of the circles . The circle (representing the total) may be divided into various sections or segments which is the certain proportion or percentage of the total  . It is also known as circular diagram.

Steps on Excel :

Steps: Select the data which you want to show in pie diagram. See below print shot for an example: We selected A1:B4

Now, click on insert tab and select 2-D pie graph.

Pie Graph is inserted. What we like to know is the percentage of divided section. Add data labels as in below:

From format data label dialog box , select percentage and other option as you like :-

Note: Column D (Angle) and column E (Percentage), we calculated just to verify the graph section percentage..

Formula for Column D (Angle at the centre ) = (component value/total value)*360 degrees.
i.e. =B2/$B$5*360

The angle at the centre of the circle is 360 degrees . And the total data is obviously represented 100 percentage. Which implies that 1 percentage is equal to 3.6 degrees. So, if we divide the data in angle terms by 3.6 and multiply by 100, we get the data in percentage terms.

Formula for Column E (Percentage)  = D2/360*100

Percentage Bar Diagram

Percentage Bar Diagram is same as sub-divided or component bar diagram (Previous Blog) except that it represent the data in percentage. It is useful to show relative importance of the various component parts to the whole. That is, the total for each part is taken as 100 and the value of each component is expressed in percentage of the respective total.

To prepare it on Excel, First create a column to expressed the data in percentage. As in example we created a column of Expenditure percentage, with formula -

=(B2/$B$8) 

And, as data is presented on graph , it would be easy to check with cumulative percentage. So, we also created cumulative percentage column with formula -

=SUM($C$2:C2)

See Below diagram:-

Now, Select column A and C as shown in below figure.

Select Insert tab and click on '100% Stacked Column'. As 100% Stacked Column is for to compare the percentage that each value contributes to a total across categories by using vertical rectangles.

By, clicking on '100% Stacked Column', our graph looks like as in below diagram. After that from Chart Tools, Select Design tab and click on 'Switch Row/Column'as we need the Expenditure percentage on vertical axis.

As , by clicking on Switch Rows/Column, we get our graph as below which we need.

After that, need some formatting part of graph. We Add data Labels by clicking mouse third button and select "Add Data Labels".

Again, click mouse third button for Format Data Labels .

In Format Data Labels box , select Label Options and select Series Name.

Here, we get our Percentage Bar Diagram, you can do more formatting as per your need.

Thank you!

Sub - Divided or Component Bar Diagram :

Sub -Divided or component bar diagrams are to be used if the total magnitude of the given variable is to be divided into various parts or sub-classes or components. The use of sub-divided bar diagram is not suggested if the number of components exceeds 10. For such, pie or circle diagram is appropriate.


Step: First, select data. See below figure as an example :

From Insert tab, click on Column "Stacked Column' icon.

 Stacked column is useful if the data is in value , not in percentage. By stacked column, we get the below figure.

But, we need comparision of Family A and Family B, which should be on Vertical axis. For that, from chart tools, click on switch rows/column as shown below:

Now, we can see different components default represent by different colors, which you can format as per your choice. Now, add labels to show which color represent which component. Click mouse third button and select "Add Data Labels", which is shown below in diagram :

By adding the data labels, we get the value . To show the component data, again click mouse third button, and click on Format Data Labels :

And in Format Data Labels box, click on Label option , and from label contains select "Series Name ".

Here, we have sub-divided diagram shown below :

Thank you!

Broken Bars Graph

Broken Bar Graph:-

Broken Bar are used for graphical presentation of the data which contain very wide variations in the values i.e the data which contain very large observations along with small observations. And in this case , simple bar graph is not much useful because it will not reveal the true characteristics of small values. In other to provide reasonable and adequate shape of smaller bar it is better to break the larger bar at the top to provide the true characteristics.

Unfortunately, in Excel there is no straightforward to insert the graph of broken bars. In order to represent it on Excel, personally I tried to use simple trick . Hopefully, it would be helpful.

Step : Select the data , from insert tab insert the scatter 'Scatter with only markers' graph.

The following data relates to the imports of foreign merchandise of Indian merchandise (in million rupees) for the year 1975-76.

And it would like as below:

Then, from insert tab click on bar "Clustered Bar' icon.

By clicking on 'Clustered Bar' icon, below is illustration it would come as. Clearly , USA Imports had larger bar, so we need to break it, for better presentation.

For broken bars, we made formula based another Import column. In column C, we used formula,

=If(B2<9000 , B2 , B2-9000)

From Chart Tools, Design tab, click on Select Data icon.

In Select Data Source box, in chart data range - change the range from $A$1:$B$13 to $A$1:$C$13. And click OK. It would appear as below:

Click on horizontal axis, click mouse third button, click on format axis. Put Minimum value as 0 and Maximum Value as 6000 as for example.

Select Imports Bar on chart. And from Chart Tools select Format. And click on Shape Fill. Choose same color as on Imports 1 bar (here it is red color). 

After that click on Insert tab, and from Shapes icon, select a Rectangle. And place it over the difference of Imports and Imports1 bar respective of particular variable. Here it is only case of variable USA. Placed over it.

Format the rectangle color as solid white to match the background color.

Select the Import1 bar, and from Chart Tools, select Layout and click on Format selection.

And in Format Data Series. Select Series Option. And in Series Overlap select Overlapped (as100%). And click close.

Format Gridlines and add data labels. Here , we get broken bar graph.

Thank you!

Simple Bar Graph : Use of Scatter and Column Chart.

Simple Bar Diagram:

Simple Bar Diagram , is the simplest way to represent the data.

So, while learning it on Excel, first we insert the scatter graph of the data and then insert column data. Let see step wise step.

Below is the data which we proceed to represent it on graph. First Select the data as in illustration.

Then , from insert tab, click on scatter "Scatter with only markers" icon.

Below diagram represent the scatter graph of data.

After pointing out the magnitudes of the observations, we need data to be represented by the heights of the rectangles.

For that, From insert tab again,  Click on Column "Clustered column".

And here, we get the representation of  "Simple bar graph".

You can do more work on formatting the graph for more presentable.

Like add data labels from layout tab:

Can remove Gridlines, from layout tab.

That's how we did it, prepared our simple bar graph.

Thank you!