How to extract data

Question

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

I'd want nothing but the number that are in between the slashes, so `s1 167.4/164.2/172.0/165.8/162.5/165.6 ` would become `167.4/164.2/172.0/165.8/162.5/165.6` — JohnSmith, Nov 22 '16 at 06:46

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

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.

edited May 23 '17 at 12:09

Community

1
1

answered Nov 22 '16 at 06:50

Shep

638
3
15

I have tried Regex, but in the end I've still got no idea how it works.. – JohnSmith Nov 22 '16 at 07:14

score 0 · Accepted Answer · answered Nov 22 '16 at 11:44

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

How to extract data

2 Answers2