I have a column with text. I'd like to retrieve all of the 3 and 4
digit numbers from it and put it into a new column/columns. For
example:
The 5 kids ate 348 hot dogs -> 348
3248 kids ate 4000burgers -> 3248, 4000
Is there a way of doing this using Excel commands or VBA?
Jeff
This can be done simply with a VBA User Defined Function using Regular Expressions:
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), with your data in column A, enter a formula like
=GetNums($A1,COLUMNS($A:A))
in some cell, and fill right for as many cells as there might be number in the sentence.
The "COLUMNS(.." argument will auto-increment to give a value corresponding to the Index in the function, so as to pull out the desired number from the string.
====================================
Option Explicit
Function GetNums(s As String, Optional I As Long = 1) As Variant
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.MultiLine = True
.Pattern = "(?:^|\D)(\d{3,4})(?=\D|$)"
End With
Set mc = re.Execute(s)
If mc.Count >= I Then
GetNums = mc(I - 1)
Else
GetNums = ""
End If
End Function
==================================
