# Sort Book Titles (sort grammatic order)

#### Petecal

##### New Member
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.

Pete

Last edited:

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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

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.

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

Warship, JoMo,

Great solution. Thanks.

Pete

****,

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:

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

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

Replies
5
Views
269
Replies
2
Views
484
Replies
0
Views
1K
Replies
5
Views
248
Replies
2
Views
250

1,211,985
Messages
6,105,221
Members
447,957
Latest member
Basildon

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

### Which adblocker are you using?

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

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