Excel formula with finding last set of characters

Is this portion "1709-567890", always of fixed length? And portion before it?

It would help if you could give realistic variation that can occur.

Other than that, if structure is always like 1 to 3...
=MID(A1,32,11)

Thanks CK76 for the advise...

So the portion "1709-567890" is a user entered field... What the spreadsheet is doing is looking at folders being created on the system.. those numbers are being picked up by the VBA code and imported as it is into the excel sheet.

So the ideal scenario is that they should all be 11 characters, but user error could exceed or enter to little of the filename if they enter it incorrectly. I tried using the MID formula but for the above is the reason is why I cant.

The portion before currently is always "\\test\blablabla\Inbound\1709\1709-567890" unless it changes - it is on a shared network, so unless they change the process (which I doubt) it will remain that way.

So as you can see the variation could be anything from below:

\\test\blablabla\Inbound\1709\1709-567890
\\test\blablabla\Inbound\1709\1709-567890\
\\test\blablabla\Inbound\1709\1709-567890\abc
\\test\blablabla\Inbound\1709\1709-567890\abc\def
\\test\blablabla\Inbound\1709\1709-5678901111
\\test\blablabla\Inbound\1709\1709-56789011\
\\test\blablabla\Inbound\1709\1709-5679

so on and so forth...

Try this one
Enter in B2 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B
1
2	\\test\blablabla\Inbound\1709\1709-567890	1709-567890
3	\\test\blablabla\Inbound\1709\1709-567890\	1709-567890
4	\\test\blablabla\Inbound\1709\1709-567890\abc	1709-567890
5	\\test\blablabla\Inbound\1709\1709-567890\abc\def	1709-567890
6	\\test\blablabla\Inbound\1709\1709-5678901111	1709-5678901111
7	\\test\blablabla\Inbound\1709\1709-56789011\	1709-56789011
8	\\test\blablabla\Inbound\1709\1709-5679	1709-5679

Thank you Alkey, I tried the formula as it is but this did not work at all, I wasn't sure how the formula works at all i'm afraid to rectify this.

CK76 - That worked a treat... I did create my own formula prior to see yours which was extremely complex. It was to check for the word "bound\"+6, if it errors, then return blank, then repeat the process again but to find the next bracket, if this errors, then sum the length of my string minus my last \. This would have got me my 4 digit number. I repeated the process to extract the 11 digit number. It looks something like this:


Extracting the 4 digit number:
I first extracted the word Inbound / Outbound into B2, if this does not exist then it will return me blank

Please Login or Register  to view this content.

Extracting the next set of numbers (if any)

Please Login or Register  to view this content.

After playing about with the substitute, I've managed to make it even more efficient and easier to read (although I have saved the one above for future reference for myself). I am now using the real data set, where it for the first 4 digits, it would be after the 8th \ occurrence.

Extracting the 4 digit code using substitute
If the 8th \ errors, return me no results, if there are only 8 \ then return me the last row of data at the end of the dataset otherwise look for the 8th and 9th bracket and give me everything in between the 2 brackets.

Please Login or Register  to view this content.

The same was applied to the 11 digit incident number... but looking for the 9th and 10th occurrence of the \

Please Login or Register  to view this content.

Thanks again for the help. I have only ever used substitute once previously but never really understood how it works.. I now need to make it dynamic so I will add this to the parameter page so only certain users can make the changes if required.