How to split data in a cell to numbers and text

English
#1

I have data from a software like 101212John, Need to split this data as 101212 and John. How it can be done in libre office calc.

#2

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

#3

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.