wildcard substring search

[blossomthe2nd]

Hi Guys

I need a formula that looks at a cell and sees if it has CGB within the text and if so will return the full 16 digits inclusive of CGB, for ex

What Id like is
COLUMN A Formula Result
CGB1234567891011 ADE CGB1234567891011
ADEFE CGB1234567891011 CGB1234567891011
asw CGB1234567891011 fasfs CGB1234567891011

Can anyone help please ?
Thanks

 

[blossomthe2nd]

Sorry the format is off a little on original post , I hope you get the jist

 

[VoG]

Try

=IF(ISNUMBER(FIND("CGB",A1)),MID(A1,FIND("CGB",A1),16),"")

 

[blossomthe2nd]

Thanks VoG

Works perfect

 

[jasonb75]

Maybe

=IF(ISNUMBER(FIND("CGB",A1)),MID(A1,FIND("CGB",A1),16),"")

 

[Sektor]

UDF alternative.

[COLOR="Blue"]Function[/COLOR] ExistsCGB(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b\w*CGB\w*\b"
        [COLOR="Blue"]If[/COLOR] .test(Str) [COLOR="Blue"]Then[/COLOR] ExistsCGB = .Execute(Str)(0)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Biz]

UDF alternative.

[COLOR=blue]Function[/COLOR] ExistsCGB(Str [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b\w*CGB\w*\b"
        [COLOR=blue]If[/COLOR] .test(Str) [COLOR=blue]Then[/COLOR] ExistsCGB = .Execute(Str)(0)
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

If I have ADEFE CGB1234567891011555 the ur Function breaks down.
I justed tweaked it and gives CGB1234567891011

Function ExistsCGB(Str As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[\b\w*CGB\w*\b]{16}"
        If .test(Str) Then ExistsCGB = .Execute(Str)(0)
    End With
End Function

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl22 height=17 width=210></TD></TR></TBODY></TABLE>

 

[Sektor]

Ah, sorry. Function returns 19 digits. Here's corrected version:

[COLOR="Blue"]Function[/COLOR] ExistsCGB(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b\w*CGB\w*\b"
        [COLOR="Blue"]If[/COLOR] .Test(Str) [COLOR="Blue"]Then[/COLOR] ExistsCGB = Left(.Execute(Str)(0), 16)
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[pgc01]

If I have ADEFE CGB1234567891011555 the ur Function breaks down.
I justed tweaked it and gives CGB1234567891011

Function ExistsCGB(Str As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[\b\w*CGB\w*\b]{16}"
        If .test(Str) Then ExistsCGB = .Execute(Str)(0)
    End With
End Function

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ebe9ed; BORDER-LEFT: #ebe9ed; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ebe9ed; BORDER-RIGHT: #ebe9ed" class=xl22 height=17 width=210></TD></TR></TBODY></TABLE>

Hi Biz

You cannot use a character class ([...]) in this case to specify all the characters of the string, for ex., because the order is important in the letter part, "CGB".

Your udf will extract things like:

AAAAAAAAAAAAAAAA
1212121212121212

 

[pgc01]

Ah, sorry. Function returns 19 digits. Here's corrected version:

[COLOR=blue]Function[/COLOR] ExistsCGB(Str [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b\w*CGB\w*\b"
        [COLOR=blue]If[/COLOR] .Test(Str) [COLOR=blue]Then[/COLOR] ExistsCGB = Left(.Execute(Str)(0), 16)
    [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Function[/COLOR]

Hi Sektor

If I understand the format correctly, by looking at the examples, it's "CGB" followed by 13 digits.

Your code will accept things like:

ABCGBJLOIJUDFR