1

How can I find in the first character and then the numeric value in this string 100App100? The formula I've used so far is below which is finding the the first numeric value. I want to return the value 100, after the App, as I want to use the formula for work for all other cells as well, example when string is FOUR200, then it would be 200. Is this possible?

=VALUE(RIGHT(B2, LEN(B2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))+1))
user15676
  • 123
  • 2
  • 10

1 Answers1

1

1] As per your described, you can using Lookup+Right function

In C1, copied down :

=LOOKUP(9^9,0+RIGHT(B1,ROW($1:$250)))

enter image description here

2] Or, should you want to extract 1st group of numeric value after 1st group of text value

then in C1, copied down :

=LOOKUP(9^9,0+MID(MID(B1,AGGREGATE(15,6,FIND(CHAR(ROW($1:$26)+{64,96}),B1),1),99),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(B1,AGGREGATE(15,6,FIND(CHAR(ROW($1:$26)+{64,96}),B1),1),99)&1/17)),ROW($1:$250)))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Instead of using `ROW($1:$250)` You could used something like `ROW(INDIRECT("1:"&LEN(B1)))` At least it seems to work for me and should work for an arbitrarily large set. – Andrew Mar 10 '22 at 18:18