Sort Book Titles (sort grammatic order)

[Petecal]

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

 

[Warship]

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]

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

=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.

 

[Warship]

Battles! Shown up again!
Nice one JoeMo...

 

[Petecal]

Warship, JoMo,

Great solution. Thanks.

Pete

 

[Petecal]

****,

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)=

 

[Warship]

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]

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