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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Petecal

New Member
Joined
Dec 5, 2010
Messages
17
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Warship, JoMo,

Great solution. Thanks.

Pete
 

Petecal

New Member
Joined
Dec 5, 2010
Messages
17
Office Version
  1. 2007
Platform
  1. Windows
****,

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:

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
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))"
 

Petecal

New Member
Joined
Dec 5, 2010
Messages
17
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

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
Top