Forum Discussion

Mike Emberley's avatar
Mike Emberley
Copper Contributor
May 03, 2018

Need help with formula IF,ISNUMBER

I am being perplexed on how to accomplish something that would seem easy on the surface, but is bugging me to no end now.  I have a dataset that contains a code that consists of letters and numbers, ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 03, 2018

And if to find only first number in text that could be like (not array formula)

=MID(A1,
   AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1),
   AGGREGATE(15,6,
      FIND(CHAR(ROW(A58:A126)),
          RIGHT(A1, LEN(A1)-AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1),1) +1) 
      ),1
   )-1
)

First AGGREGATE finds the position of first digit in the string; second one the position of first letter or special character in the rest of the text, finally MID between them.

Resources