Formula to sort text cells

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,943
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You could make an extra column.

Put there RIGHT(A1,3)

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

Pekka
:eek: :eek:
 
Upvote 0
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 {}.
 
Upvote 0
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 ! :biggrin: :biggrin:

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
:eek: :eek: :eek:
 
Upvote 0
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!
 
Upvote 0
Pekkavee said:
Cool, Andrew
It works ! :biggrin: :biggrin:

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
:eek: :eek: :eek:

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.
 
Upvote 0
Strange :confused: :confused:

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
:eek: :eek:
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
 
Upvote 0
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.
 
Upvote 0
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
:eek: :eek: :eek:
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Back
Top