Req:Extract Number from String

[ravi2628]

Hi Every One

i have a Query i need to extract numbers from String in the following format

gvsdgsdfgs TT:1000264 & TT:1000273 & (1000167) <tbody> </tbody>

1000264 <tbody> </tbody>

1000273 <tbody> </tbody>

1000167 <tbody> </tbody>

<tbody>
</tbody>

Can any help me out

 

[JoeMo]

Here's a macro you can try. Assumes your Query strings are in col A starting in A1. If you want to extract the numbers as numbers leave the + 0 (in red) in place, but note that it will strip leading zeroes from your numbers. Remove the + 0 to return strings which will leave leading zeroes in place.

Sub ExtractNums()
For i = 1 To Cells(Rows.Count, "A").End(xlUp).row
       With CreateObject("VBScript.RegExp")
              .Global = True
              .Pattern = "\d+"
              If .test(Cells(i, "A")) Then
                     For Each Match In .Execute(Cells(i, "A"))
                            ct = ct + 1
                            Cells(i, ct + 1) = Match + 0  'remove the + 0 to return strings if you have leading zeroes
                     Next Match
              End If
       End With
       ct = 0
Next i

End Sub

 

[ravi2628]

HI Joeme,

Sorry i dont want VBA Code I need is formula

because The Subordinate dont know how to use this code

but thanks for the giving me the VBA Code.

 

[Rick Rothstein]

Hi Every One

i have a Query i need to extract numbers from String in the following format

gvsdgsdfgs TT:1000264 & TT:1000273 & (1000167) <tbody> </tbody>

1000264 <tbody> </tbody>

1000273 <tbody> </tbody>

1000167 <tbody> </tbody>

<tbody>
</tbody>

If all of the numbers you want to extract are always 7 digits long as in your posted example, then you should be able to use the following formula. Assuming you data starts in cell A1, put this formula in cell B1 and copy it across for as many columns as numbers as you ever expect to have, then copy all those cells down to the bottom of your data...

=LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"(",":"),":",REPT(" ",300)),COLUMNS($B:B)*300,300)),7)

 

[ravi2628]

If all of the numbers you want to extract are always 7 digits long as in your posted example, then you should be able to use the following formula. Assuming you data starts in cell A1, put this formula in cell B1 and copy it across for as many columns as numbers as you ever expect to have, then copy all those cells down to the bottom of your data...

=LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"(",":"),":",REPT(" ",300)),COLUMNS($B:B)*300,300)),7)

thank you sir it fulfilled my request very much