Segregation specific numbers from given text or sentense

[harinsh]

Hi Team,

I hope anyone can help me on the formula or vba function to segregate the specific logic numbers from the given text ..here I mentioned input data and output and based on logic

[TABLE="width: 500"]
<tbody>[TR]
[TD] [TABLE="width: 220"]
<tbody>[TR]
[TD="class: xl65, width: 220"]Input Data[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Output should be[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]Count of Digits[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD="class: xl65, width: 230"]Logic Description[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="class: xl65, width: 220"]PO 9124818 /34 managed[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]9124818[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 93"]
<tbody>[TR]
[TD="class: xl65, width: 93"]7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD="class: xl65, width: 230"]Start from 9 and it will be there 7 digits[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="class: xl65, width: 220"]RO 31248184 11/9 taken from goods[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]31248184[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD="class: xl65, width: 230"]Start from 3 and it will be there 8 digits[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="class: xl65, width: 220"]lOK 189897 should be fine at 9/123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]189897[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD][TABLE="width: 230"]
<tbody>[TR]
[TD="class: xl65, width: 230"]Start from 1 and it will be there 6 digits[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if this doable or not ...Thank you..

 

[Norie]

What is the 'logic'?

Is it simply the set of contiguous digits in the cell?

 

[njimack]

Excel Workbook
	A	B
1	PO 9124818 /34 managed	9124818
2	RO 31248184 11/9 taken from goods	31248184
3	lOK 189897 should be fine at 9/123	189897
Sheet1

 

[harinsh]

Thank for your response...I should have explained little more details ....the position of numbers vary from each cell and need one formula to get the output based on three criteria...
for example in A1 should contain "PO /34 managed 9124818 " then the given formula will not help ...so, need one formula to get the output ....

 

[harinsh]

Hi Norie, The logic what I am trying explain here....the position of given set numbers vary from cell to cell ...sometime it may start at beginning and sometime it may start at end...any position it may start...logic is

if cell contains "PO 9124818 /34 managed" then output should be 9124818 ...if the number start from 9 then need to extract complete 7 digits including 9
if cell contains "RO 31248184 11/9 taken from goods" then output should be 31248184 ..if the number start from 3 then need to extract complete 8 digits including 3 ...
if cell contains "lOK 189897 should be fine at 9/123" then output should be 189897 ..if the number start from 1 then need to extract complete 6 digits including 1

one important thing is the position of number vary from cell to cell ...I do not know ..it could be anywhere in the given text but need the output what I mentioned above...

it would really help if you could provide the solution to this...thank you

 

[Rick Rothstein]

Thank for your response...I should have explained little more details ....the position of numbers vary from each cell and need one formula to get the output based on three criteria...
for example in A1 should contain "PO /34 managed 9124818 " then the given formula will not help ...so, need one formula to get the output ....

You might want to answer Norie's question in Message #2 so that we know why you want 9124818 and not 34.

 

[Eric W]

This formula will find the longest number in the cell:

	A	B
Input Data	Number
PO 9124818 /34 managed
RO 31248184 11/9 taken from goods
lOK 189897 should be fine at 9/123
PO /34 managed 9124818

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

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

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

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

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

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

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

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

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

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=MAX(IFERROR(MID(SUBSTITUTE(A2," ","A"),ROW(INDIRECT("1:"&LEN(A2))),TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2)))))+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]



There are still lots of cases where it might fail. Numbers over 15 digits will lose some due to Excel's 15-precision. If you have multiple numbers, it may pick the wrong one. Anytime you have inconsistent formatting, it's problematic trying to pick out the particular data you want.

 

[harinsh]

What is the 'logic'?

Is it simply the set of contiguous digits in the cell?

Not really, need number what I mentioned in the cell

 

[Rick Rothstein]

Not really, need number what I mentioned in the cell

But why that number and not the other number in the cell.


A Generalized "Please Note"
--------------------------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[harinsh]

Hi Eric, Many thanks for your response...looks it works many times...but still if anything followed further number for example "18988713/14/2007" if I want to try to get the 189887 but it will gives me 18988713

Is there any way to get rid of this as if its start from 1 it should only give 6 digits including starting number 1