Today lets tackle a very familiar problem. You have a bunch of very long, complicated file names & paths. Your boss wants a list of files extracted from these paths, like below:
Of course nothing is impossible. You just need correct ingredients.
I cannot help you with a strong cup of coffee, so go and get it. I will wait…
Back already? well, lets start the formula magic then.
Extracting file name from a path
If you observe the file paths carefully, to extract the file name, we need to know,
- Position of last \ in the full path text
Of course there are many methods find where the last \ is. You can find a very excellent summary of these techniques in our formula forensics #21 – finding the 4th slash.
Today, let us see a new technique (well, sort of).
Finding the position of last \ using formulas
Before writing any formula, first let me clarify the only assumption:
- File path is in cell B4
Now, last \ is nothing but first \ when read from right.
Read that line again.
Got it? Good, lets move on.
How do we find the first \ from right?
If we can list down all individual characters from path right to left, then we just have to find the first \ in that.
Listing down individual characters from a given text
To get 5th character from text in B4, we can use MID formula like this:
=MID(B4,5,1)
Suppose you want both 5th and 6th characters from B4, you can use:
=MID(B4,{5,6},1)
This formula returns an array of 5th and 6th characters from the text in B4.
Cool, extending the logic, =MID(B4, {6,5},1) would give 6th & 5th characters in B4.
Idea!
If we can replace {6,5} with decreasing numbers starting from length of text B4 all the way to 1, then we can list all characters in B4, right to left.
But this leads us to next problem – listing numbers from a specific value (length of B4) to 1 in descending order.
Listing numbers from n to 1 in that order
We can use ROW() formula to generate sequence of numbers like this:
=ROW(1:10) will give {1,2,3…,10}
note: this returns an array, so you need to use it with Ctrl+Shift+Enter
So if we can use =ROW(1:LEN(B4)) we could get numbers from 1 to length of text in B4 {1,2….LEN(B4)}
Unfortunately this will not work as 1:LEN(B4) is not a valid reference.
But we can fix that with INDIRECT, like this:
=ROW(INDIRECT(“1:” & LEN(B4)))
Tip: INDIRECT formula lets you construct a reference by using values in other cells as shown above.
Alternative: You can also use OFFSET to get the same result like this: =ROW(OFFSET($A$1,,,LEN(B4))). More on OFFSET here.
But wait…
So far, we have only generated numbers from 1 to n. But we need numbers from n to 1.
No sweat, we just subtract the numbers {1,2…n} from n+1 to get the list {n,n-1,n-2….2,1}
Like this:
=LEN(B4)+1 – ROW(INDIRECT(“1:” & LEN(B4)))
Using these numbers to list characters in file path in reverse order
Take a sip of that coffee, its getting cold!
Now, lets integrate our numbers in to MID like this:
=MID(B4, LEN(B4)+1 – ROW(INDIRECT(“1:” & LEN(B4))), 1)
The blue portion gives you numbers {n…2,1}
The orange portion gives you letters from right to left.
But we wanted the last \
Oh right. We do not need these letters from right to left. We instead want to find the last \ in our file path. So now we just ask Excel where the first \ is in this reversed text.
=MATCH(“\”, MID(B4, LEN(B4)+1 – ROW(INDIRECT(“1:” & LEN(B4))), 1), 0)
Blue portion gives you letters in reverse order
Orange portion finds the first \ in that.
Tip: Learn more about MATCH formula.
Extract the file name
Once you know where the last \ is, finding the file name is easy.
use =MID(B4, position_of_last_slash + 1, LEN(B4))
We need to +1 because we do not want the slash in our file name.
Demo of the entire formula in action
Okay, lets see all these steps in action in one go.
How to find the extension?
Extension is few letters added at the end of file to indicate its type. For example, excel files usually have xls, xlsx, xlsm as extension.
So how to find this extension?
Extension & file name are separated by a dot .
But often file name itself can have a dot.
In other words, Extension is text in the file name followed by last dot.
Sounds like same problem as finding the last \ and extracting file name. So I will skip the details.
But assuming the file name is in D4, extension can be found with =RIGHT(D4,MATCH(“.”,MID(D4,LEN(D4)-ROW(INDIRECT(“1:”&LEN(D4))),1),0))
NOTE on both formulas
Both file name & extension formulas are array formulas. This means after typing them, you need to press Ctrl+Shift+Enter to see correct result.
Bonus tip: Getting the file names & path from a folder
If you ever want to list down all files in a folder use this.
- Open command prompt (Start > Run > Cmd or Start > Cmd)
- Go to the folder using CD
- Type DIR /s/b >files.csv
- Close command prompt
Now you can see all the files in that folder in files.csv. Double click on it to open in Excel and run your magic 🙂
Download Example workbook
Click here to download the example workbook. The file uses slightly different formulas. But works just the same. Examine it and learn more.
How do you extract file names & as such?
Do you use formulas or do you rely on some other technique to extract portions of text like file names, mail addresses etc. Please share your tips & ideas using comments.
Extract often? You will dig this.
Analysts life is filled with 3 Es – extraction, exploration & explanation. And like a good assistant, Excel helps you in all 3.
If you find yourself with a shovel, bucket and boat load of data often, you are going to enjoy these articles:
23 Responses to “Shift Calendar Template – FREE Download”
Hi Chandoo,
your recent postings include only Excel 2007 templates. Unfortunately the company I work at still runs Excel 2003. Is it possible to get your awesome files in other excel version as well?
Thanks so much for your great excel stuff!
Is it possible to do this for shifts with hours instead of days? To organise a three shift day?
Thanks in advance,
Stelios
In my organization there are 45 employees i need split then into three shifts ex:A shift:14,B shift:14,C shift:14 and week off:3 kindly help me on this.
@Masthan
You need to understand what rules your company has for the various shifts / roster combinations
Chandoo, I once did a shift control spreadsheet for my team. I put one person in each line, the columns were the days. I put a shift code in each cell indicating in which shift that person should work, or if the person were out that day. I have two codes for being out. One is for vacations and one is to compensate days worked in weekends. This way I was able to count how many persons I have in each shift, how many were on vacations and how many were out compensating (that's the term we use here) weekend worked hours.
Later I included the possibility of a person be in two lines one for normal hours other for overtime. This is mainly used for planning purposes. If you would like I can send you an example. The only problem of this spreadsheet is that we don't have a person view, only this consolidated view.
Hi George, I would like to have a copy of your spreadsheet if you can share it.
Thanks in advance, Chuck
Hi Chandoo,
Where is the code located ? is it VBA ? If so , how do you hide it ? Or it is .NET ?
Thx
@Idan
.
No VBA or code, it is all done with Mirrors.
Only Joking,
.
But there is no VBA or code,
It is all done with Named Formulas and Lookups.
Have alook at the cells in the calander area and Named Formulas in the Formulas, Name Manager Tab.
How can i calculate between two or more different workbooks? Please, reply me as early as possible.
@Anand
Open the workbooks you want to link to
Start a formula = and click and change between workbooks as required.
You can use the View, Switch window menu to change workbooks mid formula
The format for using workbooks is
=[Workbook.xlsm]Sheet1!$A$1
or
=SUM('[Book2.xls]Sheet1'!$A$1:$D$10)
etc
Hi Chandoo,
I am working with a call centre wherein i ned to update at the month end 20 to 30 employees login hours which are defict to track it at the month end is very difficult is there any template which can be made to track that why on a particular day a guy who needs to be on calls was why not on calls.
Thank you so much Chandoo. This is really helping me. As usual, you rock.
What's FortyTwoDays and Calendar in Name manager?
Both are unused and FortyTwoDays doesn't make any sense.
I have a SQL db that contains records of events scheduled/completed on a particular date. Can this method ous building a calendar be used to display those events on the respective day?
Positively awesome!
I'm attempting to help a friend create a schedule for adult classes - and of course its not"paid help". Here is the scenario:
20 classes, instructor, room#, student class size, start date, number of class days (need to subtract weekends)
class
instructor
room
students
start
#days
PATH
karen
201
21
01/01/13
11
BILLING
jane
401
15
01/12/13
13
MEDISOFT
mike
301
11
01/25/13
9
he'd like to see these classes show up in different colors within the same month's calendar chart. He can draw it, but I'd like to see it done automatically through data, and I just can't visualize it, but I KNOW this will work - can you help?
Jan 🙂
Dear chandoo,
Try many way to download still can't access. Any way we want to try out 3 shifts with 3 guys in a group .eg Group A Morn, Group B Night and Group C Rest. And every each group must work on sunday to take turns. In fact we are security teams so that's why sunday is required to work. Pls guide and show how to put in the working calendar. Thank you in advance.
I've been trying to copy and/or recreate this to use in a workbook I'm doing for the transportation department I'm working for. I need to have the calendar on the first sheet in my document (it has graph's from data on another sheet). I'm trying to use it to track (with the conditional formatting) accidents and injuries. I've redone the conditional formatting to do 4 different accident types (no injury, near miss, OSHA recordable injury and work loss injury), but when I enter the formula's you have in the calendar portion where it says "DateOfFirst-FirstWeekDay" I can't figure out how you did that. Are you able to help?
I would like to use Excel to solve the following problem for a community work. I want to create a Driver schedule for a given month from a pool of volunteers for a community service. Each of these volunteers can drive only on specific days in a week. I would like to populate the driving schedule for each weekday with primary, secondary and tertiary drivers in a random fashion so that I do not overburden one person. I would greatly any help you can provide.
Hi chandoo,
Thanks for your valuable effort for create this template and let me know how to add multiple employees in the the Roaster.
Hi Chandoo,
This article on shift roaster is very helpful. Could you please let me know how i can use the same for n number of resources who work 24/7, considering their leaves and holidays?
Thanks,
Savitha
Hi Chandoo,
This article on shift roaster is very helpful to all. Could you please let me know how i can use the same if I want to add for some more shifts, since the color is not getting change if I add more shifts like 4,5 etc.,
Thanks,
Murali
nice post
How can I change the date to 2017 under Shift Data worksheet.