Sort on a text field

Question

Sort on a text field

Mary Eleanor Whelan 20

How to sort an excel file with location field of 1, 2A,, 3B, 20A, and 30B and not 1, 2A, 20A, 3B and 30B?

0 comments

Answer accepted by question author

EmilyS726 268.6K Independent Advisor

Hello

I am afraid there's no direct way to sort it like that. It will require helper columns to achieve this result.

Assuming your data lives on A column, say A2 cell with A1 being the header.

Then on B2 cell, put in this formula:

=VALUE(LEFT(A2,IF(ISNUMBER(--RIGHT(A2,1)),LEN(A2),LEN(A2)-1)))

Then on C2 cell, put in this formula:

=IF(ISNUMBER(--RIGHT(A2,1)),"",RIGHT(A2,1))

Then you can sort B column from smallest to biggest number, and it should then sort A column with the result you want. see screenshot for example:

User's image

Mary Eleanor Whelan 20 Reputation points

2026-06-25T23:17:21.86+00:00

Thank you for your suggestion. I had a friend suggest this.. to add a column H with this formula

=VALUE(TEXTBEFORE(G2,{"A","B"},,1,,G2))

where my 2A or 30B was in column G. I dragged the formula down the column H to copy it. It changed the sort order of column G.

3 additional answers

Your answer

Mary Eleanor Whelan 20 Reputation points

2026-06-25T23:17:21.86+00:00

Thank you for your suggestion. I had a friend suggest this.. to add a column H with this formula

=VALUE(TEXTBEFORE(G2,{"A","B"},,1,,G2))

where my 2A or 30B was in column G. I dragged the formula down the column H to copy it. It changed the sort order of column G.

Answer 1

Ashish Mathur 102.4K Volunteer Moderator

Hi,

Enter this formula in cell B2

=SORTBY(A2:A6,1*REGEXEXTRACT(A2:A6,"\d+"))

Hope this helps.

User's image

0 comments

Answer 2

IlirU 2,651 Volunteer Moderator

User's image

Hi @Mary Eleanor Whelan

There is no need for a helper column. There is also no need for multiple formulas, as you can get the desired result with just one formula. See the screenshot and apply the formula below.

=SORTBY(A2:A6, TEXT(SORT(--REGEXEXTRACT(A2:A6, "\d+")), "@"))

IlirU

Dana D 100 Reputation points

2026-06-26T13:17:46.46+00:00

I "think" chatgpt may be a little off because of the extra functions
IlirU 2,651 Reputation points Volunteer Moderator

2026-06-26T18:07:12.17+00:00

Since you think this way, who else can think differently?

Consult with ChatGPT the following formula also.

=DROP(SORT(HSTACK(--REGEXEXTRACT(A2:A6, "\d+"), A2:A6)),, 1)
Dana D 100 Reputation points

2026-06-26T18:11:52.8066667+00:00

Did you mean "thank you" for pointing out the logic error?
IlirU 2,651 Reputation points Volunteer Moderator

2026-06-27T06:14:36.27+00:00

No, I mean don't interfere in my answers again. See the opportunity to at least once give your own solution, even if you use ChatGPT, and leave that "solution" without removing it after 30 seconds.

Answer 3

Mary Eleanor Whelan 20

Thank you for your suggestion. I had a friend suggest this.. to add a column H with this formula

=VALUE(TEXTBEFORE(G2,{"A","B"},,1,,G2))

where my 2A or 30B was in column G. I dragged the formula down the column H to copy it. It changed the sort order of column G.

0 comments