How do I extract only 5 digit rows from cells in excel?

Question

I have a bunch of data that contains any number of 5-digit strings in completely inconsistent formats, and I want to extract these 5-digit strings (shown in bold). I am not worried about lines containing no more than 5 digits. as an example, this is the kind of data that I have in the file

Cell A1: "1. 76589 - wholesale activities. 2. 33476 - general

Cell A2: "WHOLESALE ACTIVITY ( 76589 ). SHIPPING ( 12235 ). ACTIVITY AS CONDITION ( 67333 )"

Cell A3: "1. 33476 Total 658709 annual roads Unknown 563"

I tried the usual functions SEARCH/FIND, MIN, LEFT/RIGHT/MIDbut not sure how to get them to create the result I needed, and even the text of the column do not give me the net result of

early

Answer 1

Here is a macro that will split your row into columns at your request.

The processed range is what you selected. Results are written to adjacent columns on the same row.

Depending on your work sheet setup, you may “clear” the lines in which the results will be executed before executing the extraction code.

You can also write code to automatically select data. Many examples on this forum.

---

Option Explicit
Sub Extract5Digits()
    Dim R As Range, C As Range
    Dim RE As Object, MC As Object, M As Object
    Dim I As Long

Set R = Selection
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\b\d{5}\b"
    For Each C In R
        If .test(C.Text) = True Then
            I = 0
            Set MC = .Execute(C.Text)
            For Each M In MC
                I = I + 1
                C.Offset(0, I) = M
            Next M
        End If
    Next C
End With
End Sub

---

Answer 2

Excel .

- Regex 55 VBA.

:

+---+--------------------------------------------------------------+
|   |                              A                               |
+---+--------------------------------------------------------------+
| 1 | Cell A3: "1. 33476 General. 658709 annual road. Unknown 563" |
| 2 | 33476                                                        |
+---+--------------------------------------------------------------+

Excel Alt + F11, = > " Microsoft VBScript Regular Expression 5.5".

:

Public Function Get5DigitsNumer(search_str As String)
Dim regEx As New VBScript_RegExp_55.RegExp
Dim matches
    GetStringInParens = ""
    regEx.Pattern = "[0-9]{5}"
    regEx.Global = True
    If regEx.test(search_str) Then
        Set matches = regEx.Execute(search_str)
        GetStringInParens = matches(0).SubMatches(0)
    End If
End Function

:

Sub PatternExtractor()
    Range("A2").Value = Get5DigitsNumer(Range("A1"))
End Sub

A1 5 , A2.

, , ; " A1:" 1. 76589 - . 2. 33476 - " .

. , .

Answer 3

The only way you can do this is to write a regular expression in VBA. I would recommend you look at the question .