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?

Solution 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)

Solution 2

This formula works for both of your examples:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),6)

Comments

[pan dong]

@Carol I already did but i got no help...even from stack exchange questions asked previously

[pan dong]

will this work for any text and 6 digit number in a cell?

[cbasah]

@pandong Yes it will unless your text contains ------ (6 dashes) like ------ is really nice, 456983 Good food

[pan dong]

Thank you so much, after I implement and check i will inform you and accept your answer

[QHarr]

Please make sure all code in box. Leave a line space between the end of text and start of code.

[pan dong]

Thanks but i am to accept the answer i received first

[pan dong]

@ barry houdini Thanks

[gavin]

Annoyingly, it wouldnt format the code as required.

[mschultz]

This ALMOST works, but fails on strings like "New York 64 is really nice, 456983 Good food" (where it finds "64 rea", not the 6-digit integer). The OP asks to find the "only 6 digit integer" in the string, but doesn't specify that it must be the first/only integer in the string, only that strings can only contain one 6-d integer. If you know the string contains only one integer (& that integer is 6-d), or that the 6-d integer will always be the first integer in the string, this works great, since it simply looks for the leading edge. In my use case, this was acceptable, so still helpful.

[zzipper72]

i needed to extract 5 digit job numbers from full notes, similar to zip code "30005" ..work perfect, so much smaller solution than many posts...thank you