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?
(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.)