Formula to separate number from given line of text data

[harinsh]

Hi All,

I hope anyone can help me with below request...

I have data with filed called description and which consists of unique PO number and this line item not standard data, every line item which inclusive of special character different different numbers along with PO number.

Example Data:
Cell 1 - 12/4 Item delivered on 15/07 and PO Order # 8998009 shipping in progress
Cell 2 - #PO Order #89768998
Cell 3 - Item can't deliver against PO Order #8989090

Output require:
Cell 1 - 8998009
Cell 2 - 89768998
Cell 3 - 8989090

I could only see the common is Order in each line but may not be necessarily present in each line. If I can replace data prior PO Order number then atleast I can go for left or right function.

Your help is much appreciate. Thank you,

 

[Eric W]

One idea:

	A	B
12/4 Item delivered on 15/07 and PO Order # 8998009 shipping in progress
#PO Order #89768998
Item can't deliver against PO Order #8989090
This is a test

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]8998009[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]89768998[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8989090[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]{=MAX(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),TRANSPOSE(ROW(INDIRECT("1:"&LEN(A1)))))+0,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



This formula will find the largest number in the cell. As long as the other numbers in the cell are shorter, like the 12, 4, 15, and 07 in the first cell, then this should get the number you want.

Let me know if this works.


Edit: Also, if your PO numbers have a range of say 7-12 digits, you can shorten the formula a bit, and make it more efficient, like this:

=MAX(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),{7,8,9,10,11,12})+0,0))

also with Control+Shift+Enter.

 

[TonyUK72]

=IF(LEN(A1)>50,TRIM(MID(A1,FIND("#",A1,2)+2,8)),RIGHT(A1,LEN(A1)-FIND("#",A1,2)))

Is what I came up with - No where near as elegant!!!!

 

[harinsh]

Hi Eric,

Thanks for your quick response. For some reason I'm not getting the require output with my original data. If you can give formula where I can replace prior "Order" text with blank ("") then I can use the text function to get the require out put.

example:

12/4 Item delivered on 15/07 and PO Order # 8998009 shipping in progress

want to replace underlined data with blank ("")

is it possible ?

Thanks

 

[harinsh]

Hi TonyUK72,

Thanks for your reply.... Its working fine however, what if I don't get the '#' sign for PO number and for some cells only have PO number no text. It would be good if formula works both ways....

 

[TonyUK72]

Unfortunately my formula will only work on the data you provided, and as you can see works on the position of the "#".

If your data always receives "shipping in progress" on some lines and PO numbers at the end on other lines, that could perhaps be built into the formula.

 

[harinsh]

Thank you Eric, I could see its working but not sure why Control+Shift+Enter anyhow, this will help me to segregate the numbers.....one more thing, can I segregate the two PO numbers in given description???

 

[Eric W]

The Control+Shift+Enter (CSE) means it's an array formula. A typical formula does one thing (=A1+1), an array formula does several things. My original formula breaks up the string into several pieces, the CSE tells Excel to look at all the pieces, and the MAX picks out one answer from the internal array.

As far as your other questions, if you want to cut off everything before "Order", try this:

	A	B
12/4 Item delivered on 15/07 and PO Order # 8998009 shipping in progress	# 8998009 shipping in progress

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=MID(A1,IFERROR(SEARCH("Order",A1)+6,1),999)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



And as far as segregating the numbers, I'm not sure what you want. Could you elaborate?

 

[AlKey]

Enter in B1 and copy down

[B]=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"#",REPT(" ",90)),90))," ",REPT(" ",90)),90))[/B]

Excel 2016 (Windows) 32 bit
	A	B
1	12/4 Item delivered on 15/07 and PO Order # 8998009 shipping in progress	8998009
2	#PO Order #89768998	89768998
3	Item can't deliver against PO Order #8989090	8989090
Sheet1