Version Incompatibility for a Number Extraction Formula?

[applehugger]

I have a formula that will extract the only the first consecutive string of numbers in an alphanumeric string. Here is what the data in the cells I am referencing looks like:
7 MPH (28.0%)

Here is the formula I am using to extract only the numbers before the MPH (in this case, 7):

Please Login or Register  to view this content.

In Excel 2019, this works perfectly. The problem is that I have Excel 2013 at work, and it just gives me a "#N/A" error. It doesn't make any sense to me because the LEFT, MATCH, and ISNUMBER all exist in Excel 2013. Does anyone have any idea why this may be failing to work in an older version of Excel? If it's any help, I incorporated this formula into a macro, which works perfectly in Excel 2019, but when I run it in Excel 2013, I get "Run-Time Error '438': Object doesn't support this property or method", and the line with the formula above is highlighted when debugging.

Any help is much appreciated!

[6StringJazzer]

Maybe there is something in the data, or a referenced formula, causing a ripple effect. Can you attach your file? See yellow banner at the top of the page.

[applehugger]

Sure can; probably should have done that in the first place. Here you go, and thanks for taking a look at this!

[Special-K]

Use LEN to establish if there is any spurious data embedded as mentioned above.

[Special-K]

Doesnt look like any embedded data but there are macros in that file, so something odd with them I guess.

[XLent]

with your current formula, I would expect CTRL + SHIFT + ENTER required for XL2013 (rather than committing with Enter)

however, if, as implied, you're pulling LEFT then just use:

=LOOKUP(9.99E+307,--LEFT([@[OVER LIMIT]],ROW($1:$15)))

[applehugger]

Doesnt look like any embedded data but there are macros in that file, so something odd with them I guess.

Well, good point, but I just tried copying and pasting the data to a blank worksheet, changing the cell references so it points directly to only one cell, and I still get the same issue.

I attached a spreadsheet with just this formula; all of the macros are gone. It still isn't working for me in Excel 2013.

[applehugger]

with your current formula, I would expect CTRL + SHIFT + ENTER required for XL2013 (rather than committing with Enter)

however, if, as implied, you're pulling LEFT then just use:

=LOOKUP(9.99E+307,--LEFT([@[OVER LIMIT]],ROW($1:$15)))

Dude, that worked. The Ctrl+Shift+Enter I mean. Thanks for the alternate formula though!

So this will probably expose me as a total novice, but how do I know what constitutes an array formula, which then requires Ctrl+Shift+Enter?

[XLent]

in this instance, the MATCH on the implicit array -- poor terminology, that no doubt, others can correct ;-)

latter versions of XL don't require the CSE entry to enforce the Array calculation.

you can get around it by embedding a further INDEX, e.g.

Please Login or Register  to view this content.

but a little more overhead - i.e. it doesn't improve performance whatsoever but, it simply negates the need for CSE -- useful in older versions from a robustness standpoint (i.e. easy to forget to reset the Array if editing formula)

[applehugger]

in this instance, the MATCH on the implicit array -- poor terminology, that no doubt, others can correct ;-)

latter versions of XL don't require the CSE entry to enforce the Array calculation.

you can get around it by embedding a further INDEX, e.g.

Formula: [Select Code]

=LEFT([@[OVER LIMIT]],MATCH(FALSE,INDEX(ISNUMBER(--MID([@[OVER LIMIT]],ROW($1:$94),1)),0),0)-1)

confirmed with Enter

but a little more overhead - i.e. it doesn't improve performance whatsoever but, it simply negates the need for CSE -- useful in older versions from a robustness standpoint (i.e. easy to forget to reset the Array if editing formula)

Awesome; thank you! This is a much better solution and is working perfectly with just Enter.

[6StringJazzer]

None of this explains why the original formula worked in one version of Excel but not another....

[applehugger]

In Excel 2013, you have to press Ctrl+Shift+Enter for array formulas to calculate properly. That isn't necessary in Excel 2019 (I think Excel 2016 made that change... I could be mistaken).

So all I had to do was do Ctrl+Shift+Enter in Excel 2013 instead of just hitting Enter. However, XLent's rewritten formula negated the need for that by eliminating the array and worked perfectly, as always.