How to convert category names into url alias

I have a bunch of category titles in this format:

Category 1
Category 2

The csv file that I am importing into phpmyadmin requires 1 column to be the alias and in this format:

category-1
category-2

Does anyone know how to convert a column of category names quickly in Excel (or any other program)? I really don't want to convert all 1000 of them manually.

Thanks guys!
sunshine1900Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
If there are no spaces in the category names then select the names, press CTRL+H, enter a space in the Find what field, enter a dash in the Replace with field, and click Replace All.

Kevin
sunshine1900Author Commented:
Thanks for trying...

There are spaces and there are commas. Here's an example:

Arts & Craft Supplies
Auction Companies
Bridal & Formal Wear
CDs, Books, Games & Entertainment
Electrical
Factory Outlets
Fitness Equipment & Accessories
zorvek (Kevin Jones)ConsultantCommented:
Place this code in a general code module, select the categories to edit, and run the macro FixCategories.

Public Sub FixCategories()

   Dim RegExp As Object
   Dim Cell As Range

   Set RegExp = CreateObject("vbscript.regexp")
   RegExp.Global = True
   RegExp.MultiLine = True
   RegExp.IgnoreCase = True
   RegExp.Pattern = "^([a-z,& ]+) ([0-9]+)$"
   For Each Cell In Selection
      If RegExp.Execute(Cell).Count > 0 Then
         Cell = RegExp.Replace(Cell, "$1-$2")
      End If
   Next Cell
   
End Sub

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Kevin
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

sunshine1900Author Commented:
I appreciate you skill already and I did what you said but it didn't actually do anything... I ran the macro but it  didn't change anything. One of the docs I'm trying to change is attached.

Because I'm so unfamiliar with the VBE I have attached a screen grab of that as well just incase I did something really wrong. That may better assist you.

Thanks so much.
bris-cats.xls
kevin.jpg
zorvek (Kevin Jones)ConsultantCommented:
There are no numbers on the end of the category names. Given these categories:

Arts & Craft Supplies
Auction Companies
Bridal & Formal Wear
CDs, Books, Games & Entertainment
Electrical
Factory Outlets
Fitness Equipment & Accessories

How, exactly, do you want them to appear after conversion?

Kevin
sunshine1900Author Commented:
Ah, ok. miscommunication. My apologies.  :)

I want them to appear like this:

arts-crafts-supplies
auction-companies
bridal-formal-wear
cds-books-games-entertainment

Basically no capital letters and all punctuation, commas and spaces to be replaced with -

This means url friendly
zorvek (Kevin Jones)ConsultantCommented:
Try this macro. Same instructions.

Public Sub FixCategories()

   Dim Cell As Range

   For Each Cell In Selection
      Cell = Replace(Replace(Replace(Cell, " ", "-"), "&", ""), ",", "")
   Next Cell
   
End Sub

Kevin
sunshine1900Author Commented:
Ah, you're awesome! :)

Ok, half way there... any chance of converting the capital letters to lower case, the apostrophes to nothing and -- to one -?

Book1.xls
zorvek (Kevin Jones)ConsultantCommented:
Public Sub FixCategories()

   Dim Cell As Range

   For Each Cell In Selection
      Cell = LCase(Replace(Replace(Replace(Replace(Replace(Cell, " ", "-"), "'", ""), "&", ""), ",", ""), "--", "-"))
   Next Cell
   
End Sub

Kevin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunshine1900Author Commented:
I love you, I love you. I love you.

Thank you so much.

:)
sunshine1900Author Commented:
Thanks again, you have no idea how much I appreciate your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.