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.
First time here? Check out the FAQ!
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.
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
Asked: 2015-10-15 13:03:25 +0100
Seen: 2,348 times
Last updated: Oct 16 '15
How do I export a chart in an image format from LibreOffice Calc? [closed]
Are there plans for a "papercut" project for libreoffice [closed]
Is it normal for Calc goal seek to take very long? [closed]
Please refine "Search" in Calc - implement functions in Gnumeric [closed]
LibreOffice Calc will not link to external data via internet [closed]
Is there a LibreOffice .odt, .ods viewer for Android? [closed]
Why is Calc so much slower at opening/saving files than MS-Office? (win7 x64) [closed]