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

[lukebrincat]

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.

[lukebrincat]

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.

[anon73440385]

Hello

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

Note(s)

• 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: 6.4.2.2; 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 () next to the answer.

[JohnSUN]

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

[lukebrincat]

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