Re: Use of Like to extract data
- From: Ron Rosenfeld <ronrosenfeld@xxxxxxxxxx>
- Date: Sun, 11 Mar 2007 11:02:14 -0400
On 11 Mar 2007 05:51:40 -0700, rve_52@xxxxxxxxxxx wrote:
I have to process a large number of cells looking for various strings
consisting of numbers followed by a decimal. The strings can consist
of 1, 2 or 3 digits then the decimal. I need to extract the string
if
I find it. I have tried this approach looking for a single digit and
a decimal.
strCall = ActiveCell.Value2
For iCnt = 1 To Len(strCall)
If (Mid(strCall, iCnt, 1) Like "*[0-9,.]*") Then
MsgBox iCnt
MsgBox Mid(strCall, iCnt, 2)
Exit For
End If
Next iCnt
Is there a better/faster way that I can do it without looping through
the string containing the data character by character?
Thanks for your help.
Here's one way.
You can use late binding but for maximum speed I would set a reference
(Tools/References) to Microsoft VBScript Regular Expressions 5.5.
================================
Sub test()
Dim s(3) As String
Dim i As Long
s(0) = "abc12.xy"
s(1) = "123.abc"
s(2) = "12ab456.xyz"
s(3) = "12345." 'not sure what you want here
Dim oRegex As RegExp
Dim oMatch As Match
Dim colmatches As MatchCollection
Const sPattern As String = "\d{1,3}(?=\.)"
Set oRegex = New RegExp
oRegex.Pattern = sPattern
oRegex.Global = True
For i = 0 To UBound(s)
If oRegex.test(s(i)) = True Then
Set colmatches = oRegex.Execute(s(i))
Debug.Print colmatches(0)
End If
Next i
End Sub
==============================
--ron
.
- Follow-Ups:
- Re: Use of Like to extract data
- From: rve_52
- Re: Use of Like to extract data
- References:
- Use of Like to extract data
- From: rve_52
- Use of Like to extract data
- Prev by Date: Copy paste from Excel to Outlook - Graphs, pivots & cells
- Next by Date: Re: Auto Update Pivot Table Help
- Previous by thread: Use of Like to extract data
- Next by thread: Re: Use of Like to extract data
- Index(es):
Relevant Pages
|
