Need some excel help.

[fuzzybabybunny]

I'm starting an online furniture store website with Magento.

Currently my item names are in an excel file displayed like:

10" TWIN LONG

I need to convert all of them to the following format:

10-twin-long

because this is what Magento needs for the item's url (10-twin-long.htm)

I don't have the foggiest idea what to do.

1. Needs to change all letters to lower case.
2. Needs to remove special characters like " and ' and .
3. Needs to convert spaces into -

 

[GrumpyMan]

Highlight the column and do a search and replace?

 

[GaryJohnson]

=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"""",""),"'",""),".","")," ","-"))

 

[bruceb]

Excel has a built in function called "Find and Replace" ... accessed by Ctrl + H .... Open the spreadsheet, click on the little box next to Row 1, Col 1 of the spreadsheet. Then do Ctrl + H, type in what you want to FIND and then in the Replace box type in what you want to put in it's place. That should do it, as Excel will find all references to what you asked it to find.

 

[fuzzybabybunny]

Thanks for the formula!

Unfortunately, I can't use find and replace because this thing will be constantly updated and I don't want to go Find and Replace like a bajillion different times.