col. a: c:\1\2\3\file.txt
desired result
col. b: file.txt
col. c: c:\1\2\3
of course the number of subdirectories varies as well as the string length of any directory / subdirectory.
the extension is always a 3 letter extension in this case, BUT to make it compatible allow for more (like .docx etc)
Thanks
If data are in A1.
b1
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",50)),50))
c1
SUBSTITUTE(A1,C1,"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
If you don't want the last /....
=LEFT(SUBSTITUTE(A1,C1,""),LEN(SUBSTITUTE(A1,C1,""))-1) in C1
Maybe like this, consider the text on A1, put this on B1
=LEFT(A1,LOOKUP(2^15,FIND("\"," "&A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)
and this one on C1
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1),"\",REPT(" ",255)),255))
@Glenn
Did it again! Just substitute c with b in the formulas (Can't refer to c in a formula residing in c).
@azumi
Good to have alternatives! Just correct, the first in c and the second in b.
Thank you both!
@Glen
Your solution assumes that no file name + ext exceeds 50 characters, right?
If your strings exceed that, try increasing the number - does that then work for you?Originally Posted by drgkt
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes, so far.
Can a formula find the longest file name in the range? (Then I will adjust the number accordingly).
Last edited by drgkt; 10-27-2016 at 12:11 PM.
No way to find longest file name in range? (Count characters after last occurence of "\")?
=len(A1) will return the no. of characters in A1
Just choose a really high number for the formula.
the length of the bit before the last / is largely irrelevant. If the bit after the last / can be more than 50 characters, increase the 50 to, for example:
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",100)),100))
one of the benefits of being a professional scientist: I know the power of experimentation. try it out & see what happens. If it doesn't explode, it can't be too far wrong....
If it doesn't explode.... xaxaxaxa
Yes Glenn I agree
I just wonder if there is a way to find the maximum file name string length (say in range a1:a100). That would be the number of characters after the last occurrence of "\" in each cell (filename plus period plus extension).
But how to translate that in a formula?
Try array entering this. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Dave
Instead of 100 you can use LEN(A1) in the formulae... which will work providing that the number of characters in the cell (n) plus x.n (where x= number of / in the string) is less than 32767.
Is this an excel formula limit? I mean how is it derived?
A cell can contain no more than 32767 characters (in Excel 2010)
ok. So max.n=32767, granted.
What is the need for n+x.n like you mentioned above?
That was me trying to explain (badly) what was happening. You have Excel 2002, I don't know the max number of characters you can have, but it will be fewer (much fewer) than 32767.
According to this
http://excel.tips.net/T003163_Charac...for_Cells.html
it's 1,024.Which means my formula won't work in Excel 2002 / 2003. Oversight on my part. My apologies.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks