Ask Your Question
0

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

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

Mr_7 gravatar image

updated 2016-11-18 10:55:27 +0200

Lupp gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-18 10:53:33 +0200

Lupp gravatar image

updated 2016-11-18 11:14:06 +0200

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 324 times

Last updated: Nov 18 '16