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]

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

Otherwise, before considering a macro you could still consider using a helper column & using that column as your sort column.

23 06 06.xlsm
	A	B	C	D
1	Name	Description	Other	Helper
2	text-2107	This is text-2107	Comment 1	text-000002107
3	text-21074	This is text-21074	Comment 3	text-000021074
4	text-2109	This is text-2109	Comment 2	text-000002109
5	next-31074	This is next-31074	Comment 12	next-000031074
6	next-3109	This is next-3109	Comment 11	next-000003109
Sort (2)

Cell Formulas
Range	Formula
D2:D6	D2	=LEFT(A2,FIND("-",A2))&TEXT(-MID(A2,FIND("-",A2),99),REPT(0,9))

After sorting on column D:

23 06 06.xlsm
	A	B	C	D
1	Name	Description	Other	Helper
2	next-3109	This is next-3109	Comment 11	next-000003109
3	next-31074	This is next-31074	Comment 12	next-000031074
4	text-2107	This is text-2107	Comment 1	text-000002107
5	text-2109	This is text-2109	Comment 2	text-000002109
6	text-21074	This is text-21074	Comment 3	text-000021074
Sort (2)

Cell Formulas
Range	Formula
D2:D6	D2	=LEFT(A2,FIND("-",A2))&TEXT(-MID(A2,FIND("-",A2),99),REPT(0,9))

 

[Formula11]

Thanks Peter, can confirm the speed is quick for large data (tens of thousands of rows, and 20 columns).
Some checks ... there were issues, as an example, such as sorted by character, then digit, then character again.
Will have to follow up but can't now.

Otherwise it may have to be arrays using VBA.

 

[Peter_SSs]

can confirm the speed is quick for large data

You mean with the SORTBY formula?

Thanks for the solution.

You're welcome.

 

[Formula11]

Yes with the SORTBY solution.
But I spoke too soon as saw an issue with sorting ... for example see below.

 

[Peter_SSs]

Well, we didn't have any sample data like that before!!

Previously we simply had a text value followed by a dash followed by a number & my interpretation was to sort by the text first and then by the number.
Perhaps you could explain what the logic is of your required sort, particularly with data like D-001-t which does not end with a number and 240-02-004 which does not start with text?

BTW, it would make it much easier to help if you provided sample data & expected results with XL2BB so we didn't have to manually type data to test with.

 

[Formula11]

Sorry Peter I agree, as it seems that sorting is quite nuanced.

The goal was for the Excel list to match whatever sorting occurred in Windows Explorer (Windows 10, if relevant).
Sorting in Explorer is different to Excel.
As a guess this will have to be done in VBA.
Will have to do more research before continuing I think.

Sorry if it was a bit of a time waster.

 

[johnnyL]

They are still all text values in column A.

 

[Peter_SSs]

They are still all text values in column A.

I don't think that there was ever any doubt about that was there?
What are you getting at with that comment?

 

[johnnyL]

I don't think that there was ever any doubt about that was there?
What are you getting at with that comment?

Just saying since the entire column is 'text' & no pattern to work from, It is going to be nearly impossible to solve this with a formula or VBA code, that I can think of anyway.