I'm trying to sort a text field that has both numbers and alphas in it, I am
not able to get it to sort right at all. Listed is how it is sorting followed
by how it needs to be. Any help would be appreciated.

This is how it is sorting:
1002086-A
1002-G
1003923-FB
1003-A
1003-G
1005243-01
1005-G
20-A

I need it to sort:
20-A
1002-G
1003-A
1003-G
1005-G
1002086-A
1003923-FB
1005243-01

I think in this case "helper" columns are in order

With
A1:A8 containing your posted data

Then
B1: =LEFT(A1,FIND("-",A1)-1)
C1: =SUBSTITUTE(A1,B1&"-","")

Copy those formula down through Row_8

Select A1:C8
From the Excel main menu:
<data<sort
Col_B: Ascending
Col_C: Ascending
At the option screens: Treat anything that looks like a number as a number

click the [OK] button

Then just delete the Col_B and Col_C formulas

Alternatively:
You could use a "one formula"approach
B1: ==REPT("0",8-FIND("-",A1))&A1

Then just sort on Columns A and B

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Chris B" wrote:

I'm trying to sort a text field that has both numbers and alphas in it, I am
not able to get it to sort right at all. Listed is how it is sorting followed
by how it needs to be. Any help would be appreciated.

This is how it is sorting:
1002086-A
1002-G
1003923-FB
1003-A
1003-G
1005243-01
1005-G
20-A

I need it to sort:
20-A
1002-G
1003-A
1003-G
1005-G
1002086-A
1003923-FB
1005243-01

I will try that, thank you

"Ron Coderre" wrote:

I think in this case "helper" columns are in order

With
A1:A8 containing your posted data

Then
B1: =LEFT(A1,FIND("-",A1)-1)
C1: =SUBSTITUTE(A1,B1&"-","")

Copy those formula down through Row_8

Select A1:C8
From the Excel main menu:
<data<sort
Col_B: Ascending
Col_C: Ascending
At the option screens: Treat anything that looks like a number as a number

click the [OK] button

Then just delete the Col_B and Col_C formulas

Alternatively:
You could use a "one formula"approach
B1: ==REPT("0",8-FIND("-",A1))&A1

Then just sort on Columns A and B

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Chris B" wrote:

I'm trying to sort a text field that has both numbers and alphas in it, I am
not able to get it to sort right at all. Listed is how it is sorting followed
by how it needs to be. Any help would be appreciated.

This is how it is sorting:
1002086-A
1002-G
1003923-FB
1003-A
1003-G
1005243-01
1005-G
20-A

I need it to sort:
20-A
1002-G
1003-A
1003-G
1005-G
1002086-A
1003923-FB
1005243-01

typo:
in this formula
B1: ==REPT("0",8-FIND("-",A1))&A1

Should be only 1 equal sign
B1: =REPT("0",8-FIND("-",A1))&A1

(sorry about the typo....hopefully it was obvious)

***********
Regards,
Ron

XL2002, WinXP


"Chris B" wrote:

I will try that, thank you

"Ron Coderre" wrote:

I think in this case "helper" columns are in order

With
A1:A8 containing your posted data

Then
B1: =LEFT(A1,FIND("-",A1)-1)
C1: =SUBSTITUTE(A1,B1&"-","")

Copy those formula down through Row_8

Select A1:C8
From the Excel main menu:
<data<sort
Col_B: Ascending
Col_C: Ascending
At the option screens: Treat anything that looks like a number as a number

click the [OK] button

Then just delete the Col_B and Col_C formulas

Alternatively:
You could use a "one formula"approach
B1: ==REPT("0",8-FIND("-",A1))&A1

Then just sort on Columns A and B

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Chris B" wrote:

I'm trying to sort a text field that has both numbers and alphas in it, I am
not able to get it to sort right at all. Listed is how it is sorting followed
by how it needs to be. Any help would be appreciated.

This is how it is sorting:
1002086-A
1002-G
1003923-FB
1003-A
1003-G
1005243-01
1005-G
20-A

I need it to sort:
20-A
1002-G
1003-A
1003-G
1005-G
1002086-A
1003923-FB
1005243-01

Assuming your data in A1:A8
create dummy column B and column C

In B1 =--LEFT(A1,FIND("-",A1)-1)
In C1 =RIGHT(A1,LEN(A1)-FIND("-",A1))

Data Sort Sort by select Column B Ascending Then by Column C Ascending





"Chris B" wrote:

I'm trying to sort a text field that has both numbers and alphas in it, I am
not able to get it to sort right at all. Listed is how it is sorting followed
by how it needs to be. Any help would be appreciated.

This is how it is sorting:
1002086-A
1002-G
1003923-FB
1003-A
1003-G
1005243-01
1005-G
20-A

I need it to sort:
20-A
1002-G
1003-A
1003-G
1005-G
1002086-A
1003923-FB
1005243-01