How to extract the six digit number in a cell string?

Question

I have a column with data:

New York is really nice, 456983 Good food
Beijing is awesome 678932, Nice culture.
... and so on

I want to extract the six digit numbers and place it in a column beside the original column.

There are n rows.

Is there any way to do this with a formula, without VBA?

Answer 1

Without VBA, you can use the following Excel formula assuming A1 is the cell containing the text.

=MID(A1,FIND("------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-")),6)