Extract different numbers from multiple strings-CodePudding

In a .csv spreadsheet, I have multiple strings with incrementing numerical values contained in each, and I need to extract the numbers from each string. For example, here are two strings:

DEVICE1.CM1 - 4.1.1.C1.CA_VALUE (A)

DEVICE1.CM2 - 6.7.1.C2.CA_VALUE (A)

DEVICE1.CM1 - 4.1.2.C1.CA_VALUE (A)

DEVICE1.CM1 - 4.1.2.C2.CA_VALUE (A)

DEVICE1.CM1 - 4.1.2.C3.CA_VALUE (A)

DEVICE1.CM1 - 5.1.1.C1.CA_VALUE (A)

DEVICE1.CM1 - 5.1.1.C2.CA_VALUE (A)

DEVICE1.CM1 - 5.10.1.C3.CA_VALUE (A)

DEVICE1.CM1 - 6.13.1.C10.CA_VALUE (A)

And I am looking to extract "4.1.1.C1" from the first string, and "6.7.1.C2" from the second string.

I have over 1000 strings, each with a different incremental value in the form of "#.#.#.C.#" and all of the options I have tried so far involve searching for a specific value to extract, rather than extracting all values of that general form. Is there any reasonable way to accomplish this?

CodePudding user response：

I am not a big fan of regular expressions because they are often hard to read, but this is a typical example where you should use them. Read carefully the Q&A BigBen linked to in the comments.

Function extractCode(s As String) As String
    Static rx As RegExp
    If rx Is Nothing Then Set rx = New RegExp
    rx.Pattern = "\d \.\d \.\d \.C\d"
    If rx.Test(s) Then
        extractCode = rx.Execute(s)(0)
    End If
End Function

(You will need to add the reference to the Microsoft VBScript Regular Expression library)

--> Updated my answer, you need to escape the dot, else it is a placeholder for any character and the pattern would also match something like "4x1y2zC3",

CodePudding user response：

So here goes:

MID(A1,FIND("-",A1,1) 2,(FIND("_",A1,1)-FIND("-",A1,1))-5)

CodePudding user response：

The fixed structure

(items) are always preceeded by " - " and followed by ".CA_VALUE (A)"

allows to isolate the code string via Split as follows:

consider ".CA_VALUE (A)" as closing delimiter, but change occurrence(s) to "- "
execute Split now on the resulting string using only the first delimiter (StartDelim "- ")
isolate the second token (index: 1 as split results are zero-based)

Function ExtractCode(ByVal s As String) As String
    Const StartDelim   As String = "- "
    Const ClosingDelim As String = ".CA_VALUE (A)"
    ExtractCode = Split(Replace(s, ClosingDelim, StartDelim), StartDelim)(1)
End Function