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]

Each cell may contain anyone of the string either it could contain by starting 9 or 3 or 1 ....I do not want to combined anything here...the data flows as you said A1, A2, A3, A4...so on ....when I update formula by using function what you given .....it should give me result in B1, B2, B3, B3 ..so on ...that's it

it should only show that respective number digits that's enough for me...

Hope I explained it better now...let me know if you need still any clarification I will post the input and output once again here....please

 

[harinsh]

Hi Rick, I believe you should little tweak the code to get the right out put using your UDF...

 

[István Hirsch]

Does the formula in post #17 work for you?

 

[Rick Rothstein]

Hi Rick, I believe you should little tweak the code to get the right out put using your UDF...

See if this tweaked code does what you want...

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) Like "[!0-9]1#####*" Then
      GetNum = Val(Mid(S, X, 7))
    ElseIf Mid("X" & S & "X", X) Like "[!0-9]3#######*" Then
      GetNum = Val(Mid(S, X, 9))
    ElseIf Mid("X" & S & "X", X) Like "[!0-9]9######*" Then
      GetNum = Val(Mid(S, X, 8))
    End If
    If Len(GetNum) Then Exit Function
  Next
End Function

 

[harinsh]

Its really awesome !!!!!!!!!! One last request could you please just brief on this code, so, that if any customization later I can still tweak myself (very hard) but at least will try attempt ...Thank you Rick your really excel magician

 

[harinsh]

Hi Rick,

Still I could see issue with UDF....I am getting one additional digit when number start from 9


912211511/22 - Ouput 91221151
919870110/16/17 -Output 91987011

 

[Rick Rothstein]

Hi Rick,

Still I could see issue with UDF....I am getting one additional digit when number start from 9


912211511/22 - Ouput 91221151
919870110/16/17 -Output 91987011

They will all produce one digit too many if there are more digits following what you want. The reason is I used what you posted in Message #11 which incorrectly uses the word "follow" in describing how many digits you wanted to retrieve. Here is the corrected code (using what you posted in your original message for the counts)...

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) Like "[!0-9]1#####*" Then
      GetNum = Val(Mid(S, X, 6))
    ElseIf Mid("X" & S & "X", X) Like "[!0-9]3#######*" Then
      GetNum = Val(Mid(S, X, 8))
    ElseIf Mid("X" & S & "X", X) Like "[!0-9]9######*" Then
      GetNum = Val(Mid(S, X, 7))
    End If
    If Len(GetNum) Then Exit Function
  Next
End Function

 

[harinsh]

Perfect Sir, Thank again ...

 

[harinsh]

Hi Rick,

I hope your doing good.

I need another customization in the below function....I could see am getting the multiple set of numbers in one cell. Please refer the below for example:

912211511/22In91221152 - Ouput - 912211511 91221151
91987019198701979870191989 -Output 9198701 9198701 9198701

Please let me know if any ohter alternative for the same option