Extract Invoice number, Mid, Right, Left, Replace, 10^10 etc.

[srizki]

I have string,
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\Cocohills\241005-PHOO2\0817NNJ036241005-SBOO2.xlsm
And I need to extract 0817NNJ036241005-SBOO2 here it happens to be the last in this string, I would like to know how to extract this and also, if it is not the last part of the string how will I extract then.

Regards,

 

[etaf]

any thing unique in an invoice number that excel could search for
if there is nothing unique , like its position or a specific string or in your example it has a \ at the front and a - in the string
so maybe looking for a - and then a \ before the - may give us the unique bit
can you perhaps post a dozen different examples of the likely format

 

[Rick Rothstein]

I have string,
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\Cocohills\241005-PHOO2\0817NNJ036241005-SBOO2.xlsm
And I need to extract 0817NNJ036241005-SBOO2 here it happens to be the last in this string, I would like to know how to extract this and also, if it is not the last part of the string how will I extract then.

Besides the questions 'etaf' asked you in Message #2 , I would like to know whether the will be a dot/extension after the Invoice Number when it is not after the last backslash character?

 

[srizki]

All invoices have different number,
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\cocolips\241005-PHOO2\1117NNJ036241005-SBOO2.xlsm
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\ConocoPhillips\241005-PHOO2\0317NNJ036241005-SBOO2.xlsm
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\ConocoPhillips\241005-PHOO2\0617NNJ036241005-SBOO2.xlsm
'S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\ConocoPhillips\241005-PHOO2\0317NNJ036241005-SBOO2.xlk

 

[srizki]

Hi Rick,
Thanks for your reply,
I have copied and pasted a few rows, I hope this will make myself clear.

Best regards

 

[Joe4]

If it is always the last piece (before the file extension), this should work (for an entry in cell A1):

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",200)),200)),".",REPT(" ",100)),100))

 

[srizki]

Thank you Joe4

It is working as I wanted.

Thank you.

 

[Joe4]

You are welcome!

 

[etaf]

you mentioned in your original post

I would like to know how to extract this and also, if it is not the last part of the string how will I extract then.

is it always the last part of string ? as in your examples

 

[srizki]

Yes, Etaf.