select and extract specific as part of a string in excel cell

Question

I have An Excel column which its data has string and number. like this:

"ccc2000111bbb"

"fgbn2000234hgdd"

I want to extract the 6 digits in a cell. how should I do that?

Note that the number length is the same it is 6 digits and it begins with 2000 (ad only the last three characters are different)

Hey David, this sounds like a really good opportunity to use a `regex`: http://stackoverflow.com/questions/8146485/returning-a-regex-match-in-vba-excel — Dan Wagner, Apr 23 '14 at 03:49

Ron Rosenfeld · Accepted Answer · 2014-04-23T04:18:40.370

The numbers in your cell are each 7 digits. If, in fact, you want to return the 7 digits in the cell that begin with 2000, then:

=MID(A1,FIND("2000",A1),7)

On the other hand, if the string of digits could be of any length, and not necessarily start with 2000, then

=LOOKUP(9.9E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")),ROW(INDIRECT("1:20"))))

will return the numeric value of the first string of digits in a cell (i.e. drops leading zero's) If leading zero's need to be maintained, a different formula will work.

select and extract specific as part of a string in excel cell

1 Answers1