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

 

[Sektor]

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

Yes, pattern searches for "CGB" string assuming that CGB can appear inside of string, what "\w*" does. In authors examples it's clear that CGB is always in the beginning of the string, although author didn't state that.

 

[pgc01]

Yes, pattern searches for "CGB" string assuming that CGB can appear inside of string, what "\w*" does. In authors examples it's clear that CGB is always in the beginning of the string, although author didn't state that.

I agree, the format is not well specified by the OP.

 

[Sektor]

Sorry, what is OP?

 

[pgc01]

Hi

It's the "Original Poster", the person that started the thread.

Sometimes it' also used as the "Original Post", the first post in the thread.

 

[Sektor]

Thanks!

 

[Biz]

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

Hi pgc01,

Sorry I missed that.

Biz