Sort Book Titles (sort grammatic order)

Petecal

New Member
Joined
Dec 5, 2010
Messages
17
Office Version
  1. 2007
Platform
  1. Windows
I have a worksheet that is a list of book titles. I would like to sort it in what I believe they call grammatic order. That is, alphabetically but if the title begins with "The " or "A ", ignore those characters and use the next word.
Note I included the blank after each.

So if I had titles like "The Apple" and "Orange", a sort would have "The Apple" come before "Orange".

Is there an easy way?

Oh, BTW a VBA macro is OK because I'm doing it inside a macro already.

Thanks In advance

Pete
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You could set up a helper col then sort by it?

put this in Col B
=IF(LEFT(A1,4)="the ",RIGHT(A1,LEN(A1)-4),A1)
Then sort on B
 
Upvote 0
If you are concerned only with titles that begin with "The " or "A " you can use a helper column as suggested by Warship and enter this formula in the helper column (here assumed to be column B with the titles in column A):
Code:
=IF(LEFT(A1,4)="The ",REPLACE(A1,1,4,""),IF(LEFT(A1,2)="A ",REPLACE(A1,1,2,""),A1))
copy down to the last cell in your column A titles, then sort on column B.
 
Upvote 0
Battles! Shown up again!
Nice one JoeMo...
 
Upvote 0
****,

I finally got around to trying this and I keep hitting a problem. I want to put that fromula into cell C2 so:
Cells(2, 3).FormulaR1C1 = "=IF(LEFT(A1,4)="The ",REPLACE(A1,1,4,""),IF(LEFT(A1,2)="A ",REPLACE(A1,1,2,""),A1))"

But the VBA editor won't let me use the line as is. It turns the line red and says "Expected end of statement" at "The ". I tried double quoting ""The "" and it allows that

Cells(2, 3).FormulaR1C1 = "=IF(LEFT(A1,4)=""The "",REPLACE(A1,1,4,""""),IF(LEFT(A1,2)=""A "",REPLACE(A1,1,2,""""),A1))"


but when it inserts the formula during execution it inserts ' ' around A1 everywhere. Here is a copy of the formula bar

=IF(LEFT('A1',4)="The ",REPLACE('A1',1,4,""),IF(LEFT('A1',2)="A ",REPLACE('A1',1,2,""),'A1'))

maybe I need to go to bed.

Or, on second thought, just use Cell(2,3)=
 
Last edited:
Upvote 0
Your Formula isn't R1C1. Use:

Cells(2, 3).Formula = "=IF(LEFT(A1,4)=""The "",REPLACE(A1,1,4,""""),IF(LEFT(A1,2)=""A "",REPLACE(A1,1,2,""""),A1))"
 
Upvote 0
Thanks Warship.

I get a bit rusty with VBA. I write a few macros once every 1.6 years or so. I completely forgot the uses of R1C1, Formula, etc. And I can't revive them.

It used to be that the Macro editor Help could actually help me, like back in Office 97, but not anymore. Help gives me a list that I swear was created by, "User request does not contain".

Thanks Again,

Pete
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
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