Ignore commas when alphabetizing a list of song titles

[JeannineM]

I'm creating a spreadsheet with all the sheet music we have in our music library and I've run into a problem with the alphabetical sorting. Here's an example:

A song titled "Holy, Holy, Holy" comes before "Holy Spirit, Come Now" alphabetically, but Excel is sorting it as "Holy Spirit, Come Now" and then "Holy, Holy, Holy".

Is there any way I can get Excel to ignore the commas in the titles and sort alphabetically as if they weren't there? Someone I asked in person suggested removing all the commas from the titles, but I can't really do that...they need to be there.

I've looked for answers to this question everywhere and I've found nothing, so I'd be very grateful if you could help me.

Thanks,
Jeannine

 

[Michael M]

Hi Jeannine
Have you considered putting in a "helper" column adjacent to you list.
In the helper column put the formula

=SUBSTITUTE(A1,","," ")

and copy down
Now sort by that column.
Once it is sorted delete that column