CODEWITHSUNDEEP
regexexcelexcel-formulavba
L.P.
L.P.

Reputation: 83

Extracting several strings from a range of cells

I have couple thousand of cell which I need to extract document numbers out from. Cells have no format what so ever, but every Document Number starts with "Doc#-" So for example

B2:

This is an example cell showing a doc number DOC#-12351-2432-1b and a second document DOC#-2342-RTF-DD-09, there may be several more or only one.

I am looking for a way to extract all the document numbers as individual numbers, into separate cells, line, etc. What would the best way to go about this?

Current I have some extremely rough Formula.

=MID($B2,find("DOC#-",$b2,1),FIND(" ",$b2,find("DOC#-",$b2,1))-find("Doc#-",$b2,1)

Which: Finds the first doc, find a space after it, returns the number with MID.

Thinking VBA is likely the way to solve this, but I'm stumped on how.

Upvotes: 1

Views: 89

Answers (2)

brettdj
brettdj

Reputation: 55692

A variant array/regexp approach for speed

Sub GetDoc()
Dim X
Dim objRegex As Object
Dim objRegexMC As Object
Dim objRegexM As Object
Dim lngCnt As Long
Dim lngCnt2 As Long

X = Range([a1], Cells(Rows.Count, "A").End(xlUp))
ReDim Preserve X(1 To UBound(X, 1), 1 To 100)

Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .ignorecase = True
    .Pattern = "DOC#[-\w]+"
For lngCnt = 1 To UBound(X)
    If .test(X(lngCnt, 1)) Then
    lngCnt2 = 2
        Set objRegexMC = .Execute(X(lngCnt, 1))
            For Each objRegexM In objRegexMC
                X(lngCnt, lngCnt2) = objRegexM
            lngCnt2 = lngCnt2 + 1
            Next
    End If
Next
End With

[a1].Resize(UBound(X, 1), UBound(X, 2)) = X

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96773

Select the cells you wish to process and run this small macro:

Sub qwerty()
    Dim i As Long, V As String, K As Long
    For Each r In Selection
        V = Replace(r.Value, ",", " ")
        ary = Split(V, "DOC#-")
        K = 1
        For i = 1 To UBound(ary)
            bry = Split(ary(i), " ")
            bry(0) = "DOC#-" & bry(0)
            r.Offset(0, K).Value = bry(0)
            K = K + 1
        Next i
    Next r
End Sub

It will look for either a space or a comma to terminate the document number. Here is an example of input/output:

enter image description here

Upvotes: 3

Related Questions