Ask Your Question
0

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

asked 2015-10-15 13:03:25 +0100

mail2skkp gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2015-10-16 03:03:21 +0100

Regina gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-10-15 13:03:25 +0100

Seen: 2,348 times

Last updated: Oct 16 '15