Formula to sort text cells

Well-known Member
Does anyone know of a formula that would sort a range of cells containing text. For example, I have the following in cells A1:A12

160605-B-094
160605-B-130
160605-B-026
160605-B-108
160605-B-031
160605-B-043
160605-B-078
160605-B-059
160605-B-068
160605-B-085
160605-B-012
160605-B-047

Is there a formula I could put in B1:B12 that would sort in order of the last three numbers:

160605-B-012
160605-B-026
160605-B-031
160605-B-043
160605-B-047
160605-B-059
160605-B-068
160605-B-078
160605-B-085
160605-B-094
160605-B-108
160605-B-130

Thanks,

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You could make an extra column.

Put there RIGHT(A1,3)

Then sort by that column.
And get rid of the column.

Pekka

Select B1:B12, type:

=LEFT(A1:A12,9)&TEXT(SMALL(RIGHT(A1:A12,3)+0,ROW()),"000")

and press Ctrl+Shift+Enter (not just Enter). If entered correctly Excel will surround the (array) formula with curly braces {}.

Andrew,

Thanks, works wonderful

Andrew Poulsom said:
Select B1:B12, type:

=LEFT(A1:A12,9)&TEXT(SMALL(RIGHT(A1:A12,3)+0,ROW()),"000")

and press Ctrl+Shift+Enter (not just Enter). If entered correctly Excel will surround the (array) formula with curly braces {}.

Cool, Andrew
It works !

You just have to use absolute reference \$A1:\$12 in the formula when you copy it.

Thanks a lot. I will keep this in my library.

Pekka

I see Andrew beat me to it, nevertheless, here's my solution, which is similar...

B1, copied down:

=INDEX(\$A\$1:\$A\$12,MATCH(SMALL(RIGHT(\$A\$1:\$A\$12,3)+0,ROWS(\$B\$1:B1)),RIGHT(\$A\$1:\$A\$12,3)+0,0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Pekkavee said:
Cool, Andrew
It works !

You just have to use absolute reference \$A1:\$12 in the formula when you copy it.

Thanks a lot. I will keep this in my library.

Pekka

Actually, the reference doesn't need to be absolute. Just in case you missed it, first select or highlight B1:B12, then enter the formula and confirm with CONTROL+SHIFT+ENTER.

Strange

What is happening.

I got it to work but when I inserted some extra rows above the data it no longer works in lower rows, even the references have changed.

It kind of remembers row 12.

What am I missing.

Pekka
Taulukkofunktio lajitteluun suoraan.xls
ABCD
1
2Title
3Formula
4=LEFT(A1:A12,9)&TEXT(SMALL(RIGHT(A1:A12,3)+0,ROW()),"000")
5
6
7160605-B-094160605-B-047
8160605-B-130160605-B-059
9160605-B-026160605-B-068
10160605-B-108160605-B-078
11160605-B-031160605-B-085
12160605-B-043160605-B-094
13160605-B-078#LUKU!
14160605-B-059#LUKU!
15160605-B-068#LUKU!
16160605-B-085#LUKU!
17160605-B-012#LUKU!
18160605-B-047#LUKU!
Taul1

My formula uses the ROW function on its own as an argument for the SMALL function, so it only works when the data starts in row 1. You would need to amend it to:

=LEFT(A1:A12,9)&TEXT(SMALL(RIGHT(A1:A12,3)+0,ROW(A1:A12)-ROW(A1)+1),"000")

if you want to insert rows above the data.

Andrew Poulsom said:
My formula uses the ROW function on its own as an argument for the SMALL function, so it only works when the data starts in row 1. You would need to amend it to:

=LEFT(A1:A12,9)&TEXT(SMALL(RIGHT(A1:A12,3)+0,ROW(A1:A12)-ROW(A1)+1),"000")

if you want to insert rows above the data.

Thanks Andrew, now I understand.

Pekka

Replies
6
Views
328
Replies
34
Views
531
Replies
2
Views
120
Replies
14
Views
458
Replies
2
Views
133

1,196,073
Messages
6,013,269
Members
441,758
Latest member
Abren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back