Need to Seperate a column in two, One containing Text One Contains Number

I have a long sheet full with Book details which I generate from our Oracle database etc etc and I need to sort them by LC Classification I am already using the first part of the classification not all of it

I need to Split the column as seen Below:

  • Column: TOP LINE (the one I get from the db)

  • H97

  • H103

  • HA11

  • HA1107

  • HB662

  • HB2000.2

  • Column: CLASS (I want to extract the text from the column TOP Line)

  • H

  • H

  • HA

  • HA

  • HB

  • HB

  • Column: MARK (I want to extract the digits from the Column TOP Line)

  • 97

  • 103

  • 11

  • 1107

  • 662

  • 2000.2

I am doing This to do a correct Natural Sort where I would sort first by Class Then Mark.

Currently I am using:
=MID(C5,1,2) to extract the first two Text characters (which Would Return HA or HB depending on the cell data_
=MID(C5,3,8) to extract the remaining characters

In the case when I have only 1 character of text like When I have H97 I need to change the formula as follows:
=MID(C5,1,1) Which would return H
=MID(C5,2,8) to extract the remaining characters

I was wondering of there is a better way of doing this. thanks.


assuming your values are stored in column A, starting row 3

=REGEX(A3;"([:alpha:]+)(.+)";"$1") into B3 and drag down
=REGEX(A3;"([:alpha:]+)(.+)";"$2") into C3 and drag down

See the following sample file: SplitColumn.ods


  • This solution requires at least LibreOffice version 6.2, which introduced function REGEX() - see LibreOffice 6.2 Release Notes
  • The second value is still text and does not automatically convert to a number. See function VALUE(), if you need a conversion.

Tested using LibreOffice:

Version:; Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US; Calc: threaded

Hope that helps.

If the answer helped to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

Good solution! By the way, formula for column C can be =SUBSTITUTE(A3;B3;"")

Thanks Opaque This Worked Perfectly Much Appreciated!!! wish I actually understood the syntax but as long as it works im happy! Thanks. Also JohnSUN thanks for the other alternative… Quite interesting Thanks