Finding a character type within a cell

  • Thread starter Thread starter shineboxnj
  • Start date Start date
S

shineboxnj

Is there a find or search function that will return the position of the
first number character within a cell?

for example

A1= TEST PRODUCT M123

is there a formula i could write that will return "15" being that is
the posistion in the cell that the "1" is in? I dont want to look for
the "1" in particular, just the first number in the cell.

thanks!
 
shineboxnj,

For your string in cell A1, the following array formula (entered using
Ctrl-Shift-Enter) will return 15:

=MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))),255,ROW(INDIRECT("A1:A"&LEN(A1)))))

All on one row.... watch any line breaks introduced by your newsreader.

HTH,
Bernie
MS Excel MVP
 
Back
Top