Re: Use of Like to extract data


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.

.

Relevant Pages

  • Re: Use of Like to extract data
    ... Dim RegExp As Object ... > I look for a decimal preceeded by from 1 to 3 numeric characters. ... > by a combination of a single alpha character followed by a space. ... > and the numerics leaving anything else behind. ...
    (microsoft.public.excel.programming)
  • Re: Efficient String Lookup?
    ... regexp language allowed embedded Perl code, ... The pattern is ... So the regexp engine tries the next option, ... I could put it inside a * to match all characters, ...
    (comp.lang.python)
  • Re: Regexp: m and [^[:alnum:]_] are not equivalent
    ... Sanitizing an arbitrary user input for regexp is not simple at all. ... - Find a candidate string with the standard search capabilities of the ... >> an explicit set of characters before. ... > boundaries". ...
    (comp.lang.tcl)
  • Re: looking for a webapp bruteforce video for non-techies
    ... Sure it is a dictionary/leet word variant, ... characters actually carry plenty of entropy (if mixed case and numerics ... I know all about secure passwords, entropy and stuff like ...
    (Pen-Test)
  • Re: A new twist on the last problem (D3/NT)
    ... This synonym gave me all the alphas in the record ID so for the above ... data I got 1 to 3 alpha characters, which was great as it allowed me to ... wanted just the first alpha part before the numeric, ie R, RC, SZY. ... and the following numerics can vary, but the end alpha (after the ...
    (comp.databases.pick)
Loading