Re: Use of Like to extract data
- From: rve_52@xxxxxxxxxxx
- Date: 11 Mar 2007 16:29:49 -0700
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
On Mar 11, 3:19 pm, "KL" <nospamlapink2...@xxxxxxxxxxxxxxxxx> wrote:
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...
<rve...@xxxxxxxxxxx> wrote in messagenews:1173635067.305967.117860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
On Mar 11, 12:45 pm, "KL" <nospamlapink2...@xxxxxxxxxxxxxxxxx> wrote:
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 -
.
- Follow-Ups:
- Re: Use of Like to extract data
- From: KL
- Re: Use of Like to extract data
- References:
- Use of Like to extract data
- From: rve_52
- Re: Use of Like to extract data
- From: Ron Rosenfeld
- Re: Use of Like to extract data
- From: rve_52
- Re: Use of Like to extract data
- From: KL
- Re: Use of Like to extract data
- From: rve_52
- Re: Use of Like to extract data
- From: KL
- Use of Like to extract data
- Prev by Date: Excel 2007 - Personnal CommandBars
- Next by Date: Re: Use of Like to extract data
- Previous by thread: Re: Use of Like to extract data
- Next by thread: Re: Use of Like to extract data
- Index(es):
Relevant Pages
|
