Func Search

It would depend on what it is you're actually trying to do... I very much doubt you want to know the position of the \ itself... rather you wish to use is as the start point for something else - extracting a word perhaps ?

Can you give some example strings and desired output ?

For ex... if you wanted to pull string post 3rd backslash in the following:

A1: C:\a\b\c\folder of interest\subfolder\file.xls

B1: =TRIM(MID(SUBSTITUTE(A1,"\",REPT(" ",100)),400,100))

would return "folder of interest"

The above approach can be adapted if the rules are not consistent...

Before we get too embroiled in complex formulae is there any reason as to why you don't run Text to Columns on A4 with + as delimiter -- this would split your string into individual files (A4,B4 etc...) at which point:

A8: =LEFT(RIGHT(INDEX($4:$4,ROWS(A$8:A8)),10),5)
copied down will extract the file (less extension)

I'm going offline shortly so if the TtC approach isn't viable for you (cleaner) you could use formulae still though obviously it's just a bit more convoluted...

If we assume then that A4 can't be split into separate strings and that each "sub-string" of A4 never exceed 1000 chars you could use:

B8:
=LEFT(RIGHT(TRIM(MID(SUBSTITUTE($A$4,"+",REPT(" ",1000)),1+((ROWS(B$8:B8)-1)*1000),1000)),11),5)
copy down as required

You could also think about using UDF in VBA
(bit slower perhaps but maybe cleaner)

eg:

B8:
=LEFT(RIGHT(STRINGSEARCH($A$4,"+",ROWS(A$8:A8)),11),5)

Where UDF STRINGSEARCH:

Please Login or Register  to view this content.

Hopefully one of these aforementioned approaches offer you a way forward.

Post back if not and I'm sure you will get further prompt assistance as required.

OK I'm glad you're running something you're happy with though I confess I don't know whether you're still using helpers or not... the formulae I provided were designed such that no helpers were required and the formula could be copied down to subsequent rows each extracting sequential data from the lengthy string (ie B8 file 1, B9 file 2 etc...)

It doesn't matter... as I say you're resolved... I see what you're doing now

(I did not review your formula properly at first so couldn't determine whether you were adopting a stepped approach as before using char positions, you're not which is good)

To handle errors... you have a couple of alternatives to double evaluating your FIND(SUBSTITUTE approach...

First option, validate length of string to see if LEN difference of string pre & post removal of delimiter of interest >= instance of interest... eg

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,I11,"")))<F13,"",MID(A$1,FIND("¬",SUBSTITUTE(A$1,I$11,"¬",F13))-G$10,G$10))

Another less used approach would be to use:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",MID(A$1,FIND("¬",SUBSTITUTE(A$1,I$11,"¬",F13))-G$10,G$10)))

In the above an array of two values is created (using CHOOSE with inline array) .. the 2 values will be

{"",result of formula}

The LOOKUP using Binary Search algorithm in conjunction with a big text value REPT("z",255) will return the last text value in the array of values... values of a different data type to the LOOKUP value (text) will be ignored ... this includes error...

So assuming your function return "Apple" then

LOOKUP(REPT("z",255),{"","Apple"})

Will return Apple... if you're function returned an error:

LOOKUP(REPT("z",255),{"",#VALUE!})

Will return the Null.. the error is ignored.

The LOOKUP approach is a nice approach IMO.