Re: Use of Like to extract data
- From: "KL" <nospamlapink2000@xxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Mar 2007 00:41:01 +0100
you may want to have a look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/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
<rve_52@xxxxxxxxxxx> wrote in message news:1173655789.445616.278290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 -
- 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
- Re: Use of Like to extract data
- From: rve_52
- Use of Like to extract data
- Prev by Date: Re: Use of Like to extract data
- Next by Date: Re: Excel 2007 - Personnal CommandBars
- Previous by thread: Re: Use of Like to extract data
- Next by thread: Re: Use of Like to extract data
- Index(es):
Relevant Pages
|
