Use of Like to extract data

[rve_52]

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.

 

[KL]

I guess your code can be redone like this:

Sub test0()
Dim sep As String, c
sep = Application.International(xlDecimalSeparator)
c = ActiveCell
If c Like "*" & sep & "*" Then
MsgBox Int(c)
MsgBox Mid(c, InStr(c, sep) + 1, 256)
End If
End Sub

Some faster options:

'find decimals one by one
Sub test1()
Dim rng As Range, c As Range
Dim i As Long, cnt As Long, sep As String

sep = Application.International(xlDecimalSeparator)
On Error Resume Next
With ActiveSheet
Set rng = [A1:A10]
Set c = rng(1)
End With
cnt = Application.CountIf(rng, "*" & sep & "*")
For i = 1 To cnt
Set c = rng.Find(sep, c)
MsgBox c - Int(c)
Next i
End Sub

'extract values to an array
Sub test2()
arrINT1 = [IF(MOD(A1:A10,1),INT(A1:A10),"")]
arrDEC1 = [IF(MOD(A1:A10,1),MID(A1:A10-INT(A1:A10),3,256),"")]
arrINT2 = Evaluate("IF(MOD(A1:A10,1),INT(A1:A10),"""")")
arrDEC2 = Evaluate("IF(MOD(A1:A10,1),MID(A1:A35-INT(A1:A10),3,256),"""")")
End Sub

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Ron Rosenfeld]

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

 

[rve_52]

Thank you both for your help.

I went through all of the data tyhat I have to use, and this is the
patter

A ###.

i.e. an optional Alpha Character followed by a space. They always
come together, or are absent together. Then up to 3 numeric
characters then a period.

Examples would be

F 1.
F 001.
676.
1.
14.
J 12.

Ron example works nicely and does what I need, however could you tell
me how to expand on your sPatter to deal with the examples I used
above? I've never worked with this type of matching before, so I'm
not sure how to change the pattern your showed me.

Thanks
b

 

[KL]

Not sure if I completely understand the objective, but maybe something like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "[^0-9]"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Replace(s(i), "")
End If
Next i
End With
End Sub

Note: No reference is necessary for this code.

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[rve_52]

That almost does what I need to do.

The Debug.print of s(0) shows 1 . I need to have it show F 1 . Then
I am going to take the F 1 and place it in another cell.
The Debug.print of s(1) shows 001 . I need to have it show F 001 .
The Debug.print of s(2) shows 676 which is what I'm looking to get.

Finally, one other type of examle would be

s(8) = "dfdg F 1." Here I want to get the F 1 and leave the other
characters out.

The data can have a few optional sets of chacters that I am trying to
skip over.

I look for a decimal preceeded by from 1 to 3 numeric characters.
That will be the most common set of characters that I am working
with. When I find one of those I need to pullout the 1 to 3 numeric
characters.
The data would look like:

1. I want to pull out 1
11. I want to pull out 11
100. I want to pull out 100

Sometimes decimal and the numeric character strings will be preceeded
by a combination of a single alpha character followed by a space.
Then I need to pull out the alpha character, the space and the numeric
digits.
The data would look like:

J 1. I want to pull out J 1
F 11. I want to pull out F 11
H 100. I want to pull out H 100

Finally the data will be an alpha character and a space followed by 1
to 3 digits and a decimal, all of that preceeded by some other alpha
data. In those cases I am trying to get the single alpha the space
and the numerics leaving anything else behind.
The data would look like:

XYZ J 1. I want to pull out J 1
abc F 11. I want to pull out F 11
JyH H 100. I want to pull out H 100

Thanks for all of your help
b

 

[KL]

maybe like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "([a-zA-Z] \d{1,3})|(\d{1,3})"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Execute(s(i))(0)
End If
Next i
End With
End Sub

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

That almost does what I need to do.

The Debug.print of s(0) shows 1 . I need to have it show F 1 . Then
I am going to take the F 1 and place it in another cell.
The Debug.print of s(1) shows 001 . I need to have it show F 001 .
The Debug.print of s(2) shows 676 which is what I'm looking to get.

Finally, one other type of examle would be

s(8) = "dfdg F 1." Here I want to get the F 1 and leave the other
characters out.

The data can have a few optional sets of chacters that I am trying to
skip over.

I look for a decimal preceeded by from 1 to 3 numeric characters.
That will be the most common set of characters that I am working
with. When I find one of those I need to pullout the 1 to 3 numeric
characters.
The data would look like:

1. I want to pull out 1
11. I want to pull out 11
100. I want to pull out 100

Sometimes decimal and the numeric character strings will be preceeded
by a combination of a single alpha character followed by a space.
Then I need to pull out the alpha character, the space and the numeric
digits.
The data would look like:

J 1. I want to pull out J 1
F 11. I want to pull out F 11
H 100. I want to pull out H 100

Finally the data will be an alpha character and a space followed by 1
to 3 digits and a decimal, all of that preceeded by some other alpha
data. In those cases I am trying to get the single alpha the space
and the numerics leaving anything else behind.
The data would look like:

XYZ J 1. I want to pull out J 1
abc F 11. I want to pull out F 11
JyH H 100. I want to pull out H 100

Thanks for all of your help
b

Not sure if I completely understand the objective, but maybe something like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "[^0-9]"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Replace(s(i), "")
End If
Next i
End With
End Sub

Note: No reference is necessary for this code.

 

[rve_52]

Thank you for all of your effort. That is perfect! Now I have to go
through it very carefully to get an understandiong of how it works!

Thanks
b

maybe like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "([a-zA-Z] \d{1,3})|(\d{1,3})"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Execute(s(i))(0)
End If
Next i
End With
End Sub

--
KL
[MVP - Microsoft Excel]
RU:http://www.mvps.ru/Program/Default.aspx
ES:http://mvp.support.microsoft.com/?LN=es-es
EN:http://mvp.support.microsoft.com/?LN=en-us
Profile:https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6...

That almost does what I need to do.

The Debug.print of s(0) shows 1 . I need to have it show F 1 . Then
I am going to take the F 1 and place it in another cell.
The Debug.print of s(1) shows 001 . I need to have it show F 001 .
The Debug.print of s(2) shows 676 which is what I'm looking to get.

Finally, one other type of examle would be

s(8) = "dfdg F 1." Here I want to get the F 1 and leave the other
characters out.

The data can have a few optional sets of chacters that I am trying to
skip over.

I look for a decimal preceeded by from 1 to 3 numeric characters.
That will be the most common set of characters that I am working
with. When I find one of those I need to pullout the 1 to 3 numeric
characters.
The data would look like:

1. I want to pull out 1
11. I want to pull out 11
100. I want to pull out 100

Sometimes decimal and the numeric character strings will be preceeded
by a combination of a single alpha character followed by a space.
Then I need to pull out the alpha character, the space and the numeric
digits.
The data would look like:

J 1. I want to pull out J 1
F 11. I want to pull out F 11
H 100. I want to pull out H 100

Finally the data will be an alpha character and a space followed by 1
to 3 digits and a decimal, all of that preceeded by some other alpha
data. In those cases I am trying to get the single alpha the space
and the numerics leaving anything else behind.
The data would look like:

XYZ J 1. I want to pull out J 1
abc F 11. I want to pull out F 11
JyH H 100. I want to pull out H 100

Thanks for all of your help
b

Not sure if I completely understand the objective, but maybe something like this:
Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "[^0-9]"
s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Replace(s(i), "")
End If
Next i
End With
End Sub
Note: No reference is necessary for this code.- Hide quoted text -

- Show quoted text -

 

[KL]

you may want to have a look here:
http://msdn.microsoft.com/library/d...html/2380d458-3366-402b-996c-9363906a7353.asp
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
http://www.tmehta.com/regexp/
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#regexpr_ex
http://groups.google.com/groups?as_q=regexp&as_ugroup=*excel*

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

Thank you for all of your effort. That is perfect! Now I have to go
through it very carefully to get an understandiong of how it works!

Thanks
b

maybe like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "([a-zA-Z] \d{1,3})|(\d{1,3})"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Execute(s(i))(0)
End If
Next i
End With
End Sub

--
KL
[MVP - Microsoft Excel]
RU:http://www.mvps.ru/Program/Default.aspx
ES:http://mvp.support.microsoft.com/?LN=es-es
EN:http://mvp.support.microsoft.com/?LN=en-us
Profile:https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6...

That almost does what I need to do.

The Debug.print of s(0) shows 1 . I need to have it show F 1 . Then
I am going to take the F 1 and place it in another cell.
The Debug.print of s(1) shows 001 . I need to have it show F 001 .
The Debug.print of s(2) shows 676 which is what I'm looking to get.

Finally, one other type of examle would be

s(8) = "dfdg F 1." Here I want to get the F 1 and leave the other
characters out.

The data can have a few optional sets of chacters that I am trying to
skip over.

I look for a decimal preceeded by from 1 to 3 numeric characters.
That will be the most common set of characters that I am working
with. When I find one of those I need to pullout the 1 to 3 numeric
characters.
The data would look like:

1. I want to pull out 1
11. I want to pull out 11
100. I want to pull out 100

Sometimes decimal and the numeric character strings will be preceeded
by a combination of a single alpha character followed by a space.
Then I need to pull out the alpha character, the space and the numeric
digits.
The data would look like:

J 1. I want to pull out J 1
F 11. I want to pull out F 11
H 100. I want to pull out H 100

Finally the data will be an alpha character and a space followed by 1
to 3 digits and a decimal, all of that preceeded by some other alpha
data. In those cases I am trying to get the single alpha the space
and the numerics leaving anything else behind.
The data would look like:

XYZ J 1. I want to pull out J 1
abc F 11. I want to pull out F 11
JyH H 100. I want to pull out H 100

Thanks for all of your help
b

Not sure if I completely understand the objective, but maybe something like this:

Sub test()
Dim RegExp As Object
Dim s(5) As String, i As Long
Const sPattern As String = "[^0-9]"

s(0) = "F 1."
s(1) = "F 001."
s(2) = "676."
s(3) = "1."
s(4) = "14."
s(5) = "J 12."

Set RegExp = CreateObject("vbscript.regexp")
With RegExp
.Pattern = sPattern
.Global = True
For i = 0 To UBound(s)
If s(i) Like "*#*" Then
Debug.Print .Replace(s(i), "")
End If
Next i
End With
End Sub

Note: No reference is necessary for this code.- Hide quoted text -

- Show quoted text -

 

[Ron Rosenfeld]

Thank you both for your help.

I went through all of the data tyhat I have to use, and this is the
patter

A ###.

i.e. an optional Alpha Character followed by a space. They always
come together, or are absent together. Then up to 3 numeric
characters then a period.

Examples would be

F 1.
F 001.
676.
1.
14.
J 12.

It looks like you optionally want to check for a capital letter and space
preceding your 1-3 digits and a dot.

So the change in sPattern is minimal:

Const sPattern As String = "([A-Z]\s)?\d{1,3}(?=\.)"


or:
=======================================
Option Explicit

Sub test()
Dim s(10) 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
s(4) = "F 1."
s(5) = " F 001."
s(6) = "676."
s(7) = "1."
s(8) = "14."
s(9) = "J 12."
s(10) = "dfdg F 1."

Dim oRegex As RegExp
Dim oMatch As Match
Dim colmatches As MatchCollection
Const sPattern As String = "([A-Z]\s)?\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 "S" & i, s(i), colmatches(0)
End If
Next i

End Sub
=======================================

Results:

S0 abc12.xy 12
S1 123.abc 123
S2 12ab456.xyz 456
S3 12345. 345
S4 F 1. F 1
S5 F 001. F 001
S6 676. 676
S7 1. 1
S8 14. 14
S9 J 12. J 12
S10 dfdg F 1. F 1



--ron