Finding 6 digit numbers in a text string

Hi folks,

This is my first post here on the forum.

Ok so I have a banking dataset with a 'description' field containing a random text string , in this string there is a 6 digit customer reference number Im trying to extract.

This 6 digit ref always has leading number 2, 3 or 4 and is USUALLY preceded by letters "ECN" (not always). It can appear anywhere in the string and can appear twice.

So it can look like
ECN412345
ECN 412345
ECN:412345
412345
etc

Examples of text string formats

BLAH UK LTD /RFB/ECN412345
B/O BLAH 1234/ECN412345 E2EID/NOTPROVIDED ECN412345
BLAH.COM 412345 44023345273216000N BLAH.COM 412345
BLAH TRAVEL LIMITED (NORTH /RFB/ECN:412345 BLAH TRAVEL
ECN412345
412345
etc

PS I found a useful formula on another thread ,
http://www.excelforum.com/excel-gene...xt-string.html

Ive tweaked it for 6 digits and it works but not in all scenarios

where A1 is text string cell,
=TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-5)),6)+0),"000000")

All help appreciated thanks
N.

Thank you bebo Works perfectly

Would you mind giving me a brief 'logic' to how the formula works? Id like to improve my understanding also .
If its too complicated no worries but would be great

Ah ok I think I understand now. Very good way to solve it.

Thanks again

Ok slight problem Ive noticed that where there is TWO 6 digit numbers in the string
the formula will return the first one as the result ...even if it doesnt have 'ECN' preceding text..

e.g.
Text String= 453881 ECN305080 NONREF
Formula result= 453881

I need the result to be 305080 in this case as it is the actual ECN Im looking for
...the other is a bank sort code I dont need.
Complicated I know...

Can the formula be amended to look for prefix "ECN" as first preference for result ...and IF this doesnt appear in string then take remaining 6 digit number beginning with 2,3 or 4?

Complicated I know...

So first preference
ECN123456 or ECN 123456 or ECN:123456
If not found
second preference
123456

Apologies I should have made this a requirement in my initial question.

Thanks to all !
-N