Help please in Calc to separate alpha sub-category from number

English
#1

I have a spreadsheet of a corrupted Family Tree database that I have put into some reasonable order. I now have as one of the columns (D) the Chart number and Sub-category that each record belongs to.
I need to sort this spreadsheet into the Chart number order and dates in other columns as sub-keys. The mixed numeric and alpha in this column doesn’t sort correctly.
Examples of the numbers used in this column are :-
1
4A/K
6
6A
16A
22A?
23?
234A
(many are blank )

The ? I presume is where the Chart can’t be defined.
The column in the chart starts at D2:D4340.
If I insert a new column after D ie E - I want to separate the alpha letters (including the ?) into the new column, leaving the numeric numbers in the original column D and all the alpha letters (A-Z and / and ? etc )
Where there is no alpha letter following a number the new cell in column E should be blank.
Where the cell in D is blank then both cells will be blank.
I’ve have in the past made a little use of string functions but I need help with this.
Thank you
Garth

#2

qa109770.ods (15.8 KB)

1 Like
#3

Villeroy, Thank you. That works.

#4

Villeroy,
Can you add 2 leading zeros to the result in the (1st) numreric column please.
ie :-
1 would be 001
10 would be 010
100 would stay as 100

There are no number higher that 999.

Will help the sort.
Thank you
Garth

#5

=IFNA(TEXT(VALUE(REGEX($A1;"\d+"));"000");"") returns the 3-digit text.

If you really need the numbers for arithmetics, use =IFNA(VALUE(REGEX($A1;"\d+"));"") and format the numbers.

#6

with option [x]natural sort should sort as expected, without extending to 3digits!

#7

Thanks… I don’t think I need the numbers other than to sort.

#8

The numeric strings will sort alphabetically. Numbers are sorted by their numeric value. Both sort orders are equivalent if the strings have the same amount of digits with leading zeroes.
And yes, option “natural sort” works with the original data as well.

#9

Thanks again

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.