Solved: Formula to extract string help

[Glaswegian]

Hi

I'm struggling with a formula (as I always do) to extract a string from a file path that will vary in length. The file path leads to a folder named after each month of the year. The file in question is dropped into the relevant monthly folder and is always named the same apart from the date portion - it's the date portion I'm trying to extract.

I've had to remove the exact server and path name but the number of characters is correct.

\\server07\folder3\BusinessUnitxxxxx\Business Unit xxxxxxxxxxxx\server - monthly report\2012\January\CBIMEXTJAN12.txt

I'm trying to extract JAN12 from the above example. I have this

=MID(B3,FIND("\",B3,98)+8,5)

which works until the month changes.

Thanks for your help.

[Bob Phillips]

How about this Iain?

=MID(B3,FIND(CHAR(1),SUBSTITUTE(B3,"\",CHAR(1),LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))+8,5)

[Glaswegian]

Hi xld

Brilliant - as always!

Many thanks again.

[shrivallabha]

Assuming CBIMEXT remains constant following will work as well:

=MID(B3,FIND("CBIMEXT",B3,1)+7,5)

[Bob Phillips]

Assuming CBIMEXT remains constant following will work as well:

I always assume these things are variable :)

[shrivallabha]

I always assume these things are variable :) Of course, things could be. I just took a stab because following got me interested:

The file in question is dropped into the relevant monthly folder and is always named the same apart from the date portion