Extracting a number with a specific structure from a cell

Question

In column A, I have different text in each cell.
In between the text within a cell, there is a number in a specific structure - "####.##.####"

I would like to copy this number, if it exists, to column B in the same line.
If there is more than one number with the structure in the same cell, the next numbers should be copied to column C, D, E etc. on the same line.

Sub findValues()
    
    Dim loopCounter, lastRow, nextBlank As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
        
    For loopCounter = 1 To lastRow Step 1
        
        With Sheets("Sheet2")
            nextBlank = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
            If Cells(loopCounter, 1).Value Like "[0-9]{4}.[0-9]{2}.[0-9]{4}" Then
                Cells(loopCounter, 2) = 1
            End If
        End With

    Next loopCounter
    
End Sub

You could research using [regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — BigBen, Mar 28 '22 at 13:07
Can you give some concrete examples about what else is in that cell where you try to extract it from? Did you try anying at all? See [No attempt was made](http://idownvotedbecau.se/noattempt/). — Pᴇʜ, Mar 28 '22 at 13:18
I tried to play with other codes that I have found, but couldn't find a solution. Trying now to add an example — Omri T, Mar 28 '22 at 13:33
I don't think that the "No attempt was made" is relevant here. As you can see, this is my first post. I was joining this group to get an assistance to something that might look as an easy task to others, but I couldn't find a solution for it. — Omri T, Mar 28 '22 at 13:41
@OmriT Actually It always applies. We don't need a solution that works but at least what you have researched so far and what you have tried. For example did you try to solve it with RegEx? • It is not a free code writing service here but we can easily assist you to fix some issues in your attempt or tell you what to improve or change. • So if you have tried something and it didn't work out no worries. Just tell what you did and we'll help you out. — Pᴇʜ, Mar 28 '22 at 13:48
By using VBA, you could loop through the cells, remove the closing parentheses, use the `Split` function to split each cell's string into an array, and compare each element of the array, by using the `Like` operator, with the pattern you have proposed `####.##.####`. — VBasic2008, Mar 28 '22 at 13:48
Excel does not have a suitable wildcard system for its Find method so as @BigBen recommended, its probably simpler to add a reference to 'Microsoft VBScript Regular Expressions 5.5' and then use a regex to examine the text value of each cell in turn — freeflow, Mar 28 '22 at 13:55
@VBasic2008, thank you, I'll start to learn about Split and Like functions — Omri T, Mar 28 '22 at 13:58
Thank you, @freeflow, I would go through Bigben's suggestion as well. These terms are quite new to me. — Omri T, Mar 28 '22 at 14:01

score 3 · Accepted Answer · answered Mar 28 '22 at 14:26

Split Column (Loop)

Option Explicit

Sub SplitColumnA()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet2")
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim cCell As Range
    Dim Words() As String
    Dim Sentence As String
    Dim r As Long, c As Long, n As Long
    
    For r = 2 To lRow
        Set cCell = ws.Cells(r, "A")
        Sentence = Replace(CStr(cCell.Value), ")", "")
        Words = Split(Sentence)
        For n = 0 To UBound(Words)
            If Words(n) Like "####.##.####" Then
                c = c + 1
                cCell.Offset(, c).Value = Words(n)
            End If
        Next n
        c = 0
    Next r
    
    MsgBox "Data split.", vbInformation
    
End Sub

Extracting a number with a specific structure from a cell

1 Answers1

Split Column (Loop)