ExcelBanter - Retrieve numbers that meet criteria

On Sat, 30 Jul 2011 21:37:10 -0700 (PDT), jeffrey wrote:

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