Extrapolate text from string (MID? COUNTIF?)

[Polsy84]

Hey guys,



Looking to get some input on the attached example sheet.

I'm needing to do an audit and need to compare differing sheets. I have the others down, but this one is proving tricky.


Cell A2 has the below text;

Phone Number Portin Order Need to Process, Order Id is 2021120619502743472;"12947247";"1.00000000000000000000"

I'm needing to extrapolate that number (2021120619502743472) from that string. Now, I'd normally do a '=LEFT' if the string were consistent, this one is not. As you can see from the A3 string.

**CANCELLED**CRD 12/17/21**Phone Number Portin Order Need to Process, Order Id is 2021121415491701717;"13002398";"1.00000000000000000000"

Example XLS

Is there something out that where I could have it search for a keyword, say 'Order Id is' (as that never changes) and then output the next 19/20 characters?

Keen to get your thoughts on this one.



Thanks all!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=REPLACE(LEFT(A2,FIND(";",A2)-1),1,SEARCH("Order id",A2)+11,"")

 

[Polsy84]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=REPLACE(LEFT(A2,FIND(";",A2)-1),1,SEARCH("Order id",A2)+11,"")

Bah! Sorry about that, good shout. Updated now.

Mate! That looks to have worked a treat! Honestly, its sexy!

Thank you so much, you've saved me hours here.

 

[Fluff]

Glad to help & thanks for the feedback.
Thanks also for updating your profile.