Reg Expression

[Autosys]

Hi there

I was hoping someone could please help me.

I have managed to write a bit of code that reads from one excel spreadsheet and then create a populate a new spreadsheet with this data.

One of the columns in the source spreadsheet is a description column. This column could contain any amount of text, but occasionaly in between all of this test there will be a string that looks like this: BBB000053228

The BBB will always be there but the number could be different, but always the same length.

Could someone tell me how to do a regexpression in VB to check if a cell contains a string with the above format pls? And if yes save that string to a variable as I then would like to put it into a new cell.

Thanks a lot! Sorry if this is an easy one.

 

[Autosys]

just to further clarify ....

i don't expect all of the code to be written for me but if someone could help me with the pattern then that would be great ..

something like the below maybe?

RTEReg.Pattern = "BBB????????

 

[tsuji]

If allow it being embedded with the data, this.
[tt] RTEReg.Pattern = "BBB\d{9}"[/tt]
If want stronger constraint on it being the data itself, then this..
[tt] RTEReg.Pattern = "^BBB\d{9}$"[/tt]
If B is case insensitive, then specify it further with this.
[tt] RTEReg.IgnoreCase = true[/tt]

 

[Autosys]

thanks for the info tsuji ... it gives me an idea what to look for howerver these examples don't appear to find anything.

for example .. the string i search looks like this:

"sdgfdg eqwwe234 asfdsfBBB54538765dfgsd"

x is say declared as a Bolean

i then do:

x = BBBReg.Test(String) .... but x stays false with all of the below examples .

any ideas?

 

[tsuji]

If the number of digits (totally 9) shown in the first post is not generic - as your last post shows a sequence of digit of length 8, then relax the condition:
[tt] at least one digit:[/tt]
[tt] RTEReg.Pattern = "BBB\d+"[/tt]
or
[tt] starting from any number say 7[/tt]
[tt] RTEReg.Pattern = "BBB\d{7,}"[/tt]
etc...
(As a side-note: BBB can sure represented as B{3} in the pattern that you surely know as well.)

 

[Autosys]

Perfect thanks tsuji !

 

[Autosys]

Sorry one more thing ...

I'm struggling to now save the value found as a variable so that I can use it to populate another cell .. any ideas?

Something I should also have mentioned was that what if there are 3 of these BBB's in one cell ... I would like to populate a target cell with all of these?

Hope this makes sence ?

 

[tsuji]

I am not sure what is the obstacle as you see it. if cells of indices (m,n) contains the pattern, store a copy of its value to cells of indices (p,q). The relation (m,n)->(p,q) is your biz logic, and how m,n,p,q are being shifted as well.
[tt]
with osheet 'some sheet worked on
'loop started on some m or n following some logic
if BBBReg.test(.cells(m,n) then
.cells(p,q)=.cells(m,n)
'shift the p,q for next assignment such as increment of row or column
end if
'loop end and back up
end with
[/tt]

 

[tsuji]

Amendment
The corresponding line:
[tt] if BBBReg.test(.cells(m,n)[red])[/red] then[/tt]

 

[Autosys]

thanks tsuji

i already have a loop ... i would like to know how i can store each pattern found in a variable.

for example.

if i search/test the string: "asfsBBB12345876gfff"

I would like to store "BBB12345876" in a variable.

Another example:

If I say test the string: "asfasfBBB23456789asdfasdfBBB98765432sdfgsdfg"

Then I would like to store the below in a variable as a string: "BBB23456789 & BBB98765432"

Sorry if this still doesn't make much sense ..

 

[tsuji]

If you don't heve global set to true or that you're just interested in the first match, then this.
[tt]
if BBBReg.test(.cells(m,n)) then
.cells(p,q)=BBBReg.execute(.cells(m,n))(0)
'shift the p,q for next assignment such as increment of row or column
end if
[/tt]

 

[TysonLPrice]

if "The BBB will always be there but the number could be different, but always the same length."

How about just:

Private Sub Command1_Click()

Dim strTestIt As String
    strTestIt = "BBB000053228"
    
    If InStr(1, strTestIt, "BBB") And Len(strTestIt) = 12 Then
        MsgBox strTestIt
    End If

End Sub

 

[Autosys]

thanks all ...

i have global set and would therefore like to save all instances of the pattern that i find in a variable in the below format.

"BBB89765678 & BBB09567865"

I can then use that variable to set the value of other cells ..

 

[tsuji]

If possibly more than one instance, then it is this figuratively (but you don't know how real).
[tt]
if BBBReg.test(.cells(m,n)) then
'you can declare om As Match somewhere above
for each om in BBBReg.execute(.cell(m,n))
.cells(p,q)=om
q=q+1 'for instance
next
're-adjusting p,q
end if
[/tt]

 

[Autosys]

thanks again tsuji great stuff