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..

 

[harinsh]

Hi Rick, I need those numbers as I have further requirement on those number ...looking for those numbers only (start from 9 followed by 7 digits, start from 3 followed by 8 numbers, start from 1 followed by 6 numbers including all starting numbers) other numbers not require...I hope answered your question ...

 

[Eric W]

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

The short answer is no. Even with VBA I couldn't tell how to parse that string. If there's no separation between numbers, I have no idea where to separate them. Between the 7 and 1? The 1 and 3? The 3 and /? This is what I meant when I said that inconsistent formatting makes it nearly impossible for reliable results. You, with your knowledge of what the numbers mean, might be able to make a better stab at manually picking the number, but I couldn't, let alone write a tool to do it automatically. I can't even think of a way to extract the number in most cases and flag the other cases for manual intervention.

 

[Rick Rothstein]

Hi Rick, I need those numbers as I have further requirement on those number ...looking for those numbers only (start from 9 followed by 7 digits, start from 3 followed by 8 numbers, start from 1 followed by 6 numbers including all starting numbers) other numbers not require...I hope answered your question ...

I think this UDF (user defined function) will do what you want...

[table="width: 500"]
[tr]
	[td]Function GetNum(S As String) As Variant
  Dim X As Long
  GetNum = ""
  For X = 1 To Len(S) + 2
    If Mid("X" & S & "X", X, 8) Like "[!0-9]1#####[!0-9]" Then
      GetNum = Val(Mid(S, X, 7))
    ElseIf Mid("X" & S & "X", X, 10) Like "[!0-9]3#######[!0-9]" Then
      GetNum = Val(Mid(S, X, 9))
    ElseIf Mid("X" & S & "X", X, 9) Like "[!0-9]9######[!0-9]" Then
      GetNum = Val(Mid(S, X, 8))
    End If
    If Len(GetNum) Then Exit Function
  Next
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetNum just like it was a built-in Excel function. For example,

=GetNum(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[VBABEGINER]

This is an Excellent..!! Thanks for posting Rick Sir..

I think this UDF (user defined function) will do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GetNum(S As String) As Variant
  Dim X As Long
  GetNum = ""
  For X = 1 To Len(S) + 2
    If Mid("X" & S & "X", X, 8) Like "[!0-9]1#####[!0-9]" Then
      GetNum = Val(Mid(S, X, 7))
    ElseIf Mid("X" & S & "X", X, 10) Like "[!0-9]3#######[!0-9]" Then
      GetNum = Val(Mid(S, X, 9))
    ElseIf Mid("X" & S & "X", X, 9) Like "[!0-9]9######[!0-9]" Then
      GetNum = Val(Mid(S, X, 8))
    End If
    If Len(GetNum) Then Exit Function
  Next
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetNum just like it was a built-in Excel function. For example,

=GetNum(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[VBABEGINER]

Thanks for post...Excellent solution..

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

<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%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]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]

Hi Rick, Thank for your UDF code, it is working perfectly but where if the input like "18988712/14/2017" then I am not getting any output by using this function ...is there any reason? Thank you

 

[István Hirsch]

Give this a try. You may have to convert no-break spaces into normal spaces.

Excel Workbook
	A	B
2	PO 9124818 /34 managed	9124818
3	RO 31248184 11/9 taken from goods	31248184
4	lOK 189897 should be fine at 9/123	189897
5	18988712/14/2017	189887
6	PO /34 managed 9124818	9124818
Sheet1

 

[Rick Rothstein]

Hi Rick, Thank for your UDF code, it is working perfectly but where if the input like "18988712/14/2017" then I am not getting any output by using this function ...is there any reason? Thank you

That 1 is followed by 7 digits, not the 6 you said you were interested in getting the number for. Are you indicating you wanted the 1 plus the next 6 digits even when the 1 is followed by more than 6 digits?

 

[harinsh]

My apologies if my statement makes you confusion...here is the simplified summary...when start from 1 then I should get total 6 digits including number 1.....

If start from 9 then 7 digits (total) including starting number 9
If start from 3 then 8 digits (total) including starting number 3
If start form 1 then 6 digits (total) including starting number 1

 

[Rick Rothstein]

My apologies if my statement makes you confusion...here is the simplified summary...when start from 1 then I should get total 6 digits including number 1.....

If start from 9 then 7 digits (total) including starting number 9
If start from 3 then 8 digits (total) including starting number 3
If start form 1 then 6 digits (total) including starting number 1

I understand how to combine the two columns, what I am not sure of is how to list them... you message is not clear (to me) how you want the combined column values presented to you... in a single column ("a 1" in first cell, "a 2" in second cell, etc.) or in a single cell as a comma delimited list like your first post showed. Please clarify how the combined values should be shown to you.