Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

You can do this using regular expressions. Look in Tools > Options > Calc > Calculate that the option "Enable regular expressions in formula" is enabled.

I assume, that your text 101212John is in cell A1.

Then the formula =SEARCH("[:alpha:]";A1) gives you the position of the first non-digit.

Then functions LEFT and RIGHT divide the text.

You get the number part with =LEFT(A1;SEARCH("[:alpha:]";A1)-1)

and the character part with RIGHT(A1;LEN(A1)-SEARCH("[:alpha:]";A1)+1)

For an example see the attached file RegularExpression.ods