Find position of first number (decimal digit) in a cell.

In Excel i can use the following formula

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},BE6&"0123456789"))

To return the position of the first number in cell BE6 but i can't work out how to replicate this function in Calc.

For example if the contents of the cell is "AM13" Excel returns a value of 3 but Calc returns 5.

Any ideas?

edit retag close merge delete

Sort by » oldest newest most voted

(I did not understand the &"0123456789" in your formula. Please explain if you think it is relevant/needed.)

As usual it's solved much better in Calc:
Make sure that 'Enable regular expresions in formulae' is selected under > 'Tools' > 'Options' > 'LibreOffice Calc' > 'Calculate'. Then use
=SEARCH("[0-9]";BE6) to get the position of the first decimal digit occurring in the content/result from cell BE6. If there is no digit you will get the error indicator #VALUE!. If you prefer to get a numeric result in any case, you can use
=IFERROR(SEARCH("[0-9]";BE6);0) and a zero result will indicate then "No digit found!".

Please note: If the content/result of BE6 is of type 'Number' (=ISNUMBER(BE6) returning TRUE) the suggested formula will return 2 for negative numbers and 1 otherwise, independent of the 'Numbers' format assigned to BE6.

(If you insist on the monstrous formula used in Excel, you have to enter it for array-evaluation using Ctrl+Shift+Enter.)

more

Stats

Asked: 2016-11-18 10:19:24 +0200

Seen: 324 times

Last updated: Nov 18 '16