# 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!

How to split data in a cell to numbers and text.

add a comment

1

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]

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.