formula to get the number from a string

**mrkhchan** · 06-04-2015, 01:32 AM

Hi All,

Is there any formula to get the number from a string?
Thanks.

**FDibbins** · 06-04-2015, 01:44 AM

depending on your data, either of these should work...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
=LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1) Courtesy of sktneer

Both assume your data is in A1 - adjust as needed

**mrkhchan** · 06-05-2015, 02:17 AM

Thanks, FDibbins.

Originally Posted by FDibbins

depending on your data, either of these should work...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
=LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1) Courtesy of sktneer

Both assume your data is in A1 - adjust as needed