CODEWITHSUNDEEP
excelvba
JohnSmith
JohnSmith

Reputation: 3

How to extract data

I have this problem in Excel where I need to extract data from a string of text like the one below. The data below are all on separate rows. I'm trying to extract only the data that's in between the slashes into something like x/y/z/a/b/c format and ignoring the rest of the data in front. Hopefully this problem can be resolved using vba.

An example:

s1 167.4/164.2/172.0/165.8/162.5/165.6 s2 163.6/164.1/167.2/167.1/165.6/167.6 S#1:0.1682/0.1646/0.1641/0.1653/0.1622/0.1642 S#2:0.1623/0.1621/0.1667/0.1680/0.1667/0.1643 s24=1903/1881/1873/1903/1915/1891 s25=1872/1858/1904/1879/1854/1868 SLOT 24:0.2031/0.1991/0.1959/0.1989/0.1987/0.2051 SLOT 25:0.2014/0.2019/0.2014/0.2033/0.2001/0.2017

Upvotes: 0

Views: 105

Answers (2)

Tom
Tom

Reputation: 9898

Have a look at this:

Public Sub RegExpExample()
    Dim rng As Range
    Dim c As Variant, arr As Variant
    Dim k As Long
    Dim regEx As Object
    Dim Match, Matches

    ' Uses late binding so no need to add references 
    Set regEx = CreateObject("VBScript.RegExp")

    ' Change this to match your range where your values are
    With Sheet2
        Set rng = .Range(.Cells(1, 1), .Cells(8, 1))
    End With
    ' Declare RegEx parameters
    With regEx
        .Pattern = "(?:\d*\.)?\d+"
        .IgnoreCase = True
        .Global = True
    End With
    ' Loop through each row
    For Each c In rng
        Set Matches = regEx.Execute(c)
        If Matches.Count > 0 Then
            ReDim arr(1 To Matches.Count)
            k = 1
            For Each Match In Matches
                arr(k) = Match
                k = k + 1
            Next Match
            ' Print back to worksheet
            Range(c.Offset(0, 1), c.Offset(0, UBound(arr))) = arr
        End If
    Next c

End Sub

You'll have to decide how to handle the first column of values that are returned or wait until someone that is better with RegEx puts up an answer

Upvotes: 0

Shep
Shep

Reputation: 628

Regular Expressions would be the most robust answer.

A custom VBA function is required and an expression to cover your requirements.

Detailed guide: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

A regex you could use may be: (?:\s|:|=).*

i.e. Find first space or first : or first =, then select everything after.

Upvotes: 1

Related Questions