Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 16 Oct 2015 03:03:21 +0200How to split data in a cell to numbers and text.https://ask.libreoffice.org/en/question/59501/how-to-split-data-in-a-cell-to-numbers-and-text/ 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.Thu, 15 Oct 2015 13:03:25 +0200https://ask.libreoffice.org/en/question/59501/how-to-split-data-in-a-cell-to-numbers-and-text/Answer by Regina for <p>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.</p>
https://ask.libreoffice.org/en/question/59501/how-to-split-data-in-a-cell-to-numbers-and-text/?answer=59561#post-id-59561You 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](/upfiles/14449573869737039.ods)Fri, 16 Oct 2015 03:03:21 +0200https://ask.libreoffice.org/en/question/59501/how-to-split-data-in-a-cell-to-numbers-and-text/?answer=59561#post-id-59561