Need help to understand formula

[fayo2009]

Hello,

I was searching with Google for a way to extract the numbers from a text string (different numbers of characters) and found a formula that works perfectly. As glad as I am that it solved my problem, I can't figure out how it works. Could someone explain step by step what the formula is doing? I would really appreciate it.

in this example, my text string is in cell D2

Service Invoice line for 88837799 at 520348 Kromles Back Occupant KEL C VAT Recoverable

=LOOKUP(10^6,1*MID($D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$D2&"0123456789",FIND(" at "," "&$D2&" "))),{2,3,4,5,6}))

Output- 520348

 

[Fluff]

As you have 365 you can use this simpler formula.

=--TEXTBEFORE(TEXTAFTER(D2,"at ")," ")

 

[fayo2009]

=--TEXTBEFORE(TEXTAFTER(D2,"at ")," "

Many thanks! The TEXTAFTER works.

Output:- 520348 Kromles Back Occupant KEL C VAT Recoverable

but the TEXTBEFORE doesn't seem to work I am getting #VALUE

 

[Fluff]

Maybe those are non-breaking spaces, try

=--TEXTBEFORE(TEXTAFTER(SUBSTITUTE(D2,CHAR(160)," "),"at ")," ")

 

[fayo2009]

It worked with the below.

=TEXTBEFORE(TRIM(TEXTAFTER($D2,"at"))," ")

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[fayo2009]

Couldn't have done it without your help, much appreciated.

Thanks always for your prompt responses.