Extract Numbers from text

[LokeshMani]

Hello,
I am searching the formula , where i can extract only 8 digit number from the cell.
the cell contains text with numbers.

Example 1--- 13245678 - 588 Nos, 45678934 -57 Nos--A1 cell-- Result -- I need it like--13245678 ,45678934

Example 2 -- 13245678decevv123devvb - A2 Cell- Result-- 13245678

Thanks in advance..

 

[Peter Bartholomew]

I suspect the use of worksheet formulas does not offer the best way forward. It is possible to split the string into an array of individual characters but testing for a series of 8 consecutive digits is not straightforward.

I think you are more likely to meet with success defining a UDF and using RegEx (regular expression) from the Scripting library. There is quite a lot of information available online, for example

http://ramblings.mcpher.com/Home/excelquirks/regular-expressions

Personally speaking, I find the regular expressions pretty opaque but you would require only the smallest part of the functionality. I think you might need
"\d{8}"
to select a sequence of 8 arbitrary digits or
"[1-9]{8}"
would exclude 0s.

 

[bosco_yip]

Maybe……………………

In B2, copied across and down :

=IFERROR(MID(", "&$A2,FIND("@",SUBSTITUTE(", "&$A2,",","@",COLUMNS($A:A)))+2,8),"")

Regards
Bosco