Nested mid search

[fueledbycheerios]

Hello

I have a string of text of variable length which contains an 8 digit purchase order number which I need to find. However, the format for the PO varies between “PO #”, “PO#” etc.

I am able to return the PO number using the following example formulas

=IFERROR(MID(AI5,SEARCH("PO#",AI5)+4,8),"")
=IFERROR(MID(AI5,SEARCH("PO #",AI5)+5,8),"")
=IFERROR(MID(AI5,SEARCH("PO -",AI5)+5,8),"")

What I am unable to do is make these in to a signal formula, any suggestions?

Thanks

 

[Aladin Akyurek]

Always provide example input for this class of problems...

That said, does this work for you?

=IFERROR(LEFT(TRIM(REPLACE(SUBSTITUTE(A15," ",""),1,4,"")),8),"")

 

[Scott T]

Assuming there is only one PO number in each string then this should work.

=IFERROR(MID(AI5,SEARCH("PO#",AI5)+4,8),"")&IFERROR(MID(AI5,SEARCH("PO #",AI5)+5,8),"")&IFERROR(MID(AI5,SEARCH("PO -",AI5)+5,8),"")

 

[fueledbycheerios]

Assuming there is only one PO number in each string then this should work.

=IFERROR(MID(AI5,SEARCH("PO#",AI5)+4,8),"")&IFERROR(MID(AI5,SEARCH("PO #",AI5)+5,8),"")&IFERROR(MID(AI5,SEARCH("PO -",AI5)+5,8),"")

That's worked like a charm, thanks for the help!

 

[Aladin Akyurek]

Try the shorter suggestion too...