Pulling out Code from Text String

[ccecil02]

Hello all!

I'm looking to pull out a code from the middle of a text string. The codes are always 22 characters long and come after the phrase "Code:"

Example in cell C4: Welcome! Get $X off $X purchase. Code:xxxxxxxxxxxxxxxxxxxxxxxxxx EXP {{date:MM/dd|+30d}} Excl Apply.Approx 5 auto txts/mo.TERMS=T&CsSTOP=endHELP=help Msg&DataRatesMayApply

I feel like I've tried some things I've seen on here and I get results that don't quite work when it seems they should.

Thanks in advance for your help!!

 

[philwojo]

try this:

=MID(C4,FIND(":",C4)+1,22)

Put that in the cell you want to have the information show up.

 

[ccecil02]

This is great, Phil. Thanks so much!!

try this:

=MID(C4,FIND(":",C4)+1,22)

Put that in the cell you want to have the information show up.

 

[philwojo]

Glad to help.

 

[ccecil02]

So I modified it to this, =MID(C335977,FIND("Code:",C335977)+5,23)

How would I modify it further if it's sometimes listed as "Code:" and other times as "Cod:"

 

[philwojo]

No reason to look for the "code" or "cod" part as both end in the colon ":", so just do the find on the colon.

 

[ccecil02]

No reason to look for the "code" or "cod" part as both end in the colon ":", so just do the find on the colon.

There's some other text at the beginning on the message that has a colon as well (I omitted from first post on accident) so I think that's where I'm running into an issue when keying on the colon.

 

[philwojo]

Try this then:

=IF(ISERROR(FIND(":",C4)),"No Code",IF(ISERROR(FIND("cod:",C4)),MID(C4,FIND("code:",C4)+5,22),MID(C4,FIND("cod:",C4)+4,22)))

It will spit out "No Code" if there is no colon located in C4, otherwise it will look for both "cod:" and "code:" and give you the output