Offset Filename

[ruckuz]

Hi,
I'm trying to get just the filename and i used this formula, =MID(CELL("filename",E30),90,13). the formula works fine until there's more characters then it doesn't capture what is need and i would need to change the "13" to a 14 or 15 depending on how many more characters. I know the offset formula will work but it's a tricky formula & i still haven't gotten it down.

S:\FinancialStatements\FinancialStatements\Route66\2019\02February2019[GLDetail-February2019.xlsx]Recon

<tbody>
</tbody>

Above is the file name. i'm just trying to capture the month and year of the file and like i said the mid formula works until it's the next month then i'll need to change the mid formula. what's the formula for offset so i don't need to change the formula every month? Thanks.

 

[Trevor_S]

Can you do it by finding the square brackets, e.g.:
=MID(CELL("filename",E30),FIND("[",CELL("filename",E30))+10,FIND("]",CELL("filename",E30))-FIND("[",CELL("filename",E30))-14)
You may need to adjust the +10 and -14 figures to start with (as I haven't been able to test it), but once you have the right numbers, there's no need to change them each month.

 

[DanteAmor]

Try this

=TRIM(MID(SUBSTITUTE(CELL("filename",E30),"]",REP(" ",500)),SEARCH("[",CELL("filename",E30))+1,100))

 

[ruckuz]

Thank you. It worked. I think I know what you did, a lot of counting spaces.

 

[DanteAmor]

It is right.
with pleasure. thanks for the feedback