Sort range - mix of text and numbers

[Formula11]

I'm sorting a large range of data, based on values in column A.
Column A values are a mix of text and digits, so a String.
Example below shows sorting for an example case. There's an issue when there's a different count of digits.
Is there a fix for this, like a setting in Excel.

 

[Peter_SSs]

like a setting in Excel.

No, but you have a couple of options.

If you really want the actual data sorted, then you could create a helper column as shown in col B below and sort both columns using col B as the sort column.

Otherwise, with no helper column as such you could leave the original data as is and use a formula like D2 (if the leading text in col A is always the same length) or F2 (if the leading text can vary in length)

23 06 06.xlsm
	A	B	C	D	E	F
1	Name
2	text-2107	2107		text-2107		text-2107
3	text-21074	21074		text-2109		text-2109
4	text-2109	2109		text-21074		text-21074
Sort

Cell Formulas
Range	Formula
D2:D4	D2	=SORTBY(A2:A4,MID(A2:A4,6,99)+0)
F2:F4	F2	=SORTBY(A2:A4,-MID(A2:A4,FIND("-",A2:A4),99))
B2:B4	B2	=-MID(A2,FIND("-",A2),99)
Dynamic array formulas.

 

[johnnyL]

Are you trying to sort the column using vba code or what? Please be specific in what you are asking for.

 

[Formula11]

Peter, thanks for the suggestions above.

I should have been more specific as you mention johnnyL.
- The data is large, in the tens of thousands of rows.
- There are multiple columns which have to be sorted as well.
- The prefix "text" as shown in the example above, is a variable and can be something else.
I didn't mention VBA because I thought it may take a long time, but if it can be done this way, no objection.

 

[johnnyL]

Did you try the formulas @Peter_SSs suggested? If so, what were the results?

 

[Formula11]

Yes I did, this is what eventuates.
It may be that I should follow this through and get other columns to match somehow.

 

[johnnyL]

What am I missing? It appears that your column F is the sort result of column A

 

[Formula11]

Yes but if you look at "next-#" this should be before "text-#".
But it may be enough to get me started at least. I haven't looked at it further yet.

 

[johnnyL]

ahhh, I see now, thanks for pointing out that you changed the original data, I see the problem now.

 

[Peter_SSs]

This formula approach may not suit your large data, but let's pursue it for now.

23 06 06.xlsm
	A	B	C	D	E	F	G
1	Name	Description	Other		Name	Description	Other
2	text-2107	This is text-2107	Comment 1		next-3109	This is next-3109	Comment 11
3	text-21074	This is text-21074	Comment 3		next-31074	This is next-31074	Comment 12
4	text-2109	This is text-2109	Comment 2		text-2107	This is text-2107	Comment 1
5	next-31074	This is next-31074	Comment 12		text-2109	This is text-2109	Comment 2
6	next-3109	This is next-3109	Comment 11		text-21074	This is text-21074	Comment 3
Sort (2)

Cell Formulas
Range	Formula
E2:G6	E2	=SORTBY(A2:C6,LEFT(A2:A6,FIND("-",A2:A6))&TEXT(-MID(A2:A6,FIND("-",A2:A6),99),REPT(0,9)))
Dynamic array formulas.