CODEWITHSUNDEEP
excel
Ollie
Ollie

Reputation: 337

Excel formula to find the index of ANY number within a cell

Is there an Excel formula to find the index of ANY number within a cell? For example, the cell may contain something like “Tylenol 500mg” or “Tylenol PM 300mg” or “Advil 5.55mg”. I need to know where the text ends and the numbers begin.

I’m hoping to avoid writing a macro, but will if there aren’t any existing Excel formulas. As always...Thank you!

Upvotes: 0

Views: 242

Answers (1)

Scott Craner
Scott Craner

Reputation: 152485

Use this array formula:

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

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

To deal with ones that do not have numbers at all.

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

enter image description here

Or you can use this regularly entered formula:

=AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1)

To deal with ones without numbers:

=IFERROR(AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1),0)

Upvotes: 3

Related Questions