This time I would like to show you some cool tricks that you can do in Excel. The post is inspired by Chandoo’s awesome blogpost about how to get the file name from full path, specifying how to get the last portion of a string value. Chandoo’s post includes several good trick, worth it to check it. I provide an other kind of approach to achieve the same, only with formulas, at the end of the post you’ll be able to perform these kind of string manipulation in Excel + you’ll understand it (I hope at least). Example workbook can be founded here.

If you use Excel in other language than english try the Excel translator.

Excel String Trick 1. - How many character/string portion do we have in a cell?

This example we will find out how many occurrences have the quote below.

All Dog Breeds, All Dog Types, All Dog List Names & Pictures

Wrong approaches

Let’s say the example is in A1:

=countif(A1:A1,'dog') 

The result is 0, because countif try to match exactly.

=countif(A1:A1,'*dog*') 

The result is 1, because the formula uses wildcard character, but if we evaluate it right we learned, that the cell contains at least one the characters ‘dog’. It is a good method to create flags or something.

=search('dog',A1)

The result is 5, because the search function return the index of the first character of our searchText. Be aware this function is case insensitive.

The solution

=(len(A1)-len(substitute(lower(A1),"dog","")))/len("dog")

For readers, who use Excel in hungarian, and also are lazy to use Excel translator:

=(HOSSZ(A1)-HOSSZ(HELYETTE(KISBETŰ(A1);"dog";"")))/HOSSZ("dog")

The solution is a bit tricky, let’s see it step by step

With this method we can easily count any character(s) occurences in any string.

Excel String Trick 2. - Extract file name from path

As in intro mentioned I would like to show you an other method to get file name from path than Chandoo (Really smart trick).

1. Get file names

Open a cmd (or type the command line or Total Commander) in a specific directory and execute the command below in a specific folder in order to get the file names with fullpath ( /b option is for simple or bare format)

dir /b|clip.exe

This command pipelining the folder content to the clipboard, simple press a ctrl+v after the command execution in you excel worksheet.

If you want to get the subdirectories as well, then (/s for subdirectories…)

dir /b /s|clip.exe

or you can redirect the output to a csv file

dir /b /s>files.csv

or powershell

(Get-Childitem -recurse).fullname|clip.exe

You can use any of get-childitem’s aliases like *dir; ls; gci * etc…

2. Use Excel String Trick 1. to get how many backslash are in paths there

Example string:

F:\blog\_posts\2018-11-26-excel-string-tricks.md

=(LEN(A2)-LEN(SUBSTITUTE(A1,"\","")))

Notice it we don’t need divide the result because the search string is just one character.

Result: 3

3. Locate the last backslash

To get the filename, we need everything after the last backslash. The substitue function has an optional parameter, where we can specify which occurrence would like to replace with other character.

=SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))

The optional parameter is our pervious point, with this we changed the last “\” to “@”. You can choose any other character that not present in the string.

Result: ‘F:\blog\_posts@2018-11-26-excel-string-tricks.md’

We can easily locate this newly replaced character

=SEARCH("@",SUBSTITUTE(A7,"\","@",LEN(A7)-LEN(SUBSTITUTE(A7,"\",""))))

Result: 15

4. Get the file name + All in one

So far we know the filename start after the 15th character, so we need to find out how many characters left right to it

=LEN(A1)-SEARCH("@",SUBSTITUTE(A7,"\","@",LEN(A7)-LEN(SUBSTITUTE(A7,"\",""))))

Result: 33

Get X (=previous result) character from right to get the filename

=RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))

Result: ‘2018-11-26-excel-string-tricks.md‘

The example work book is here, please leave a comment if you liked this content or whatever :)

:metal: :poop: :metal: