Want to Pull file name into a cell

[xenotrop]

Hi there,

I was wondering if anyone knew how to pull a set number of character of the spreadsheets file name into a cell.

I.e. If i want in cell A1 to specifically pull the first 9 characters of the following file name "datatrack 01022011.xslx" so that in cell A1 Datatrack would be given. Then also specifically ask for the last 8 characters in cell b1.

I know you can reference tab names in a spread sheet just not sure how its done for the actual file name.

Thanks.

 

[T. Valko]

Hi there,

I was wondering if anyone knew how to pull a set number of character of the spreadsheets file name into a cell.

I.e. If i want in cell A1 to specifically pull the first 9 characters of the following file name "datatrack 01022011.xslx" so that in cell A1 Datatrack would be given. Then also specifically ask for the last 8 characters in cell b1.

I know you can reference tab names in a spread sheet just not sure how its done for the actual file name.

Thanks.

This will return the file name:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

To get specific lengths just wrap that inside the LEFT and RIGHT functions:

=LEFT(=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),9)

=RIGHT(=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),8)

Note that the file must have been saved at least once and given a name.

 

[T. Valko]

After reading your post again it looks like you want to extract portions of the file name and to exclude the file extension.

So, a slight tweak of the formulas is in order.

This will extract the file name less the file extension:

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Then you use the same model with the LEFT and RIGHT functions.

 

[xenotrop]

Thank you for the response. However, i am unsure why, but i am getting #VALUE! as a return when i drop in the =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1).

and the left right version are similar. Any ideas?

 

[xenotrop]

Actually i modified your formula a bit. Say i have Datatrack file one.xslx and i use the following:

If i have +LEFT(MID(CELL("filename",C4),FIND("[",CELL("filename",C4))+1,255),9)

it returns datatrack. This does half of what i wanted, i.e. pull a specified number of character beginning from the left side of the file name.

Now the one i am having trouble with is if i want to pull specifically the characters that spell out file. I assume the formula works by counting empty spaces as characters as well, so the word file would be characters 11,12,13, and 14 in the sample file name.

Not sure if its possible to pull a specific range within the file name to a cell, but here is to hoping!

Thanks.

 

[T. Valko]

Thank you for the response. However, i am unsure why, but i am getting #VALUE! as a return when i drop in the =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND(".",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1).

and the left right version are similar. Any ideas?

If the file hasn't been saved and given a name you'll get that error.

When you start Excel it loads a default template workbook named Book1.

If you enter data in that file it technically doesn't exist until you go to save the file. And when you go to save the file you have to give it a name.

That formula will not work until you have saved the file and given it a name.

 

[T. Valko]

Actually i modified your formula a bit. Say i have Datatrack file one.xslx and i use the following:

If i have +LEFT(MID(CELL("filename",C4),FIND("[",CELL("filename",C4))+1,255),9)

it returns datatrack. This does half of what i wanted, i.e. pull a specified number of character beginning from the left side of the file name.

Now the one i am having trouble with is if i want to pull specifically the characters that spell out file. I assume the formula works by counting empty spaces as characters as well, so the word file would be characters 11,12,13, and 14 in the sample file name.

Not sure if its possible to pull a specific range within the file name to a cell, but here is to hoping!

Thanks.

If you want to parse the file name with more precision then I would extract the file name to a cell of its own and then do the parsing based on the value of that cell.

If your file name (less the file extension) is Datatrack file one and is in cell A1 and you want to extract each word...

=LEFT(A1,FIND(" ",A1)-1)

Will extract Datatrack

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99))

Will extract file

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

Will extract one