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]

thanks
you have a solution then

 

[srizki]

Thanks for looking into it.

 

[Rick Rothstein]

Thank you Joe4

It is working as I wanted.

Here is another formula that you can consider (little bit shorter, 2 less function calls)...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".",""),"",REPT(" ",300)),600),300))

 

[srizki]

Thank you Rick,
I really appreciate it.
Just wondering how does it work, is there a book or website where I can learn the advance formula?

But this is the result of the formula,
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\ConocoPhillips\241005-PHOO2\1117NNJ036241005-SBOO2xlsm
It removed the ".", and did not extract the last part, 1117NNJ036241005-SBOO2


Best regards,

 

[Rick Rothstein]

But this is the result of the formula,
S:\Houston JIB Accounting\Outside Operated JIB's\Jacquie's Group JIBs\SOHAIL RIZKI UPLOADS\ConocoPhillips\241005-PHOO2\1117NNJ036241005-SBOO2xlsm
It removed the ".", and did not extract the last part, 1117NNJ036241005-SBOO2

It looks like my originally posted formula had the backslashed removed (two of them) by this forum's comment processor. I am going to try an post it again, with all the text, in a Code Box and see if the backslashes are preserved there...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".","[B][COLOR="#FF0000"]\[/COLOR][/B]"),"[B][COLOR="#FF0000"]\[/COLOR][/B]",REPT(" ",300)),600),300))

Edit Note: I just looked and the backslashes (highlighted in red) are preserved, so use what is shown in the Code Box above instead of what got posted in my earlier message.

 

[srizki]

thank you Rick