Reputation: 3
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
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
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