FORMULA to Extract Specific Numbers in a text field

Hi...Definitely need help on this one.
Do we have a formula on how to extract a specific set of numbers (loan number) on a group of text field. Usually the 1st 2 digits starts with 24 or 3 letters with TLC . Posted a sample below. Any help is greatly appreciated.

Sample 1
REF:20130813L1B78J1C000484 RELATED REF:TLC29292271 ID:001235737204 BNF BK: ID: PAYMENT DETAILS: TLC29292271 RTN SNDRF TLC29292271 DD13AUG13 UTA INVALID ACCOUNT

Sample 2
WIRE TYPE:WIRE IN DATE:081313 TIME:1129 ET TRN:2013081300162745 SNDR REF:G0132252494701 SERVICE REF:273692 ID:000759569534 247526918 RICH EDMUNDS 26175770

Sample 3
WIRE TYPE:BOOK IN DATE:081313 TIME:1215 ET TRN:2013081300175615 SNDR REF:13813A5838QN0T53 SERVICE REF: RELATED REF: ID:001235737204 BNF BK: ID: per your request john c chapman loan 247111212

Originally Posted by Craig K.

Correction to my previous post's formula to incorporate some of what msexcel mentioned. try this instead:

Formula:

Please Login or Register  to view this content.

Craig, i tried but its still picking up early numbers that begins with 24. How about if we expand the search options, like numbers that begins with 3 digits, specifically " 244,245,246,247,248,249, 233"

Originally Posted by msexcelathome

Imagine Sample 1 Being in Cell A1 - Put this in B1.

=MID(A1,IFERROR(FIND("TLC",A1),FIND(24,A1)),(IFERROR(FIND(" ",A1,IFERROR(FIND("TLC",A1),FIND(24,A1))),LEN(A1))+1-IFERROR(FIND("TLC",A1),FIND(24,A1))))

But this is error prone. For example if 24 if found earlier like in Sample 2, this is gonna mess it up.

Looking for a better solution still.

Deep

yes, your right. its picking up earlier sets of numbers that beings with 24. can we instead expand the search options that begins with 3 digits. 244,245,246,247,248,233

Originally Posted by Craig K.

Your wire messages aren't uniform which is causing a bit of a problem (by the way, posting wire messages online are a big no-no for financial institutions)

try this then

Formula:

Please Login or Register  to view this content.

It Works!! Thank you so much Craig, you are awesome!

Originally Posted by Craig K.

Just add the condition to the formula I gave you. Like this

Formula:

Please Login or Register  to view this content.

Craig, can we expand your formula to include the "#" preceded the number.

Try this version:


=IFERROR(MID(A1,SEARCH(" TLC",A1)+1,11),MID(A1,LOOKUP(1E+99,SEARCH({" 24","/24","#24"},A1)+1),9))

Thank Thank you Dear Mama!!

Originally Posted by Craig K.

Do what Mama says!

Sorry I didn't get back to you yesterday, but I don't log onto this site when I'm at home.

@Mama - I was trying to originally build what you did but never thought to bring in the LOOKUP() function. Questions for you though, Does a function that does not use an array argument (like SEARCH()) need to be used within another function that does require an array argument (like LOOKUP()) to be able to work? what does the 1E+99 argument do within the LOOKUP() function? It's the only part I don't understand. Thanks.

Thanks Craig!! you guys solved my problem! ....AWESOMENESS TO YOU BOTH!!

Originally Posted by Teethless mama

Try this version:


=IFERROR(MID(A1,SEARCH(" TLC",A1)+1,11),MID(A1,LOOKUP(1E+99,SEARCH({" 24","/24","#24"},A1)+1),9))

Hi Mama,
Just curios and like what Craig ask you, what is the use of 1E+99 argument in LOOKUP function.