Extract numbers from a string

This function will work on your data, it's a new formula you can use in this workbook.

Option Explicit

Function NUMSTR(RNG As Range, Optional Delim As String) As String
'Author:    Jerry Beaucaire
'Date:      8/24/2010
'Summary:   Pull numeric strings out of text strings, list delimited
Dim MyArr   As Variant
Dim i       As Long
Dim x       As Long
Dim cell    As Range
Dim Buf     As String
Dim Num     As String

If Delim = "" Then Delim = ","

For Each cell In RNG
    MyArr = Split(cell, " ")
    For i = LBound(MyArr) To UBound(MyArr)
        If IsNumeric(Left(MyArr(i), 1)) Then
            For x = 1 To Len(MyArr(i))
                If IsNumeric(Mid(MyArr(i), x, 1)) Then _
                    Num = Num & Mid(MyArr(i), x, 1) ' Leave only numbers
            Next x
            If Buf = "" Then Buf = Num Else Buf = Buf & Delim & Num
            Num = ""
        End If
    Next i
Next cell

NUMSTR = Buf

End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

=============
The function is used in an empty cell like so:

=NUMSTR(A1)
=NUMSTR(A1:A3)

...or

=NUMSTR(A1, ",")
=NUMSTR(A1:A3, "-")

This newer version lets you put the delimiter to use as an optional second parameter.

The function will search all the words in your text string and try to determine which are numeric and display ALL of them in a delimited string.


Attached is a sample workbook with the UDF installed and demonstrated for you.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this.

Welcome to the forum.

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.