02-22-2010, 12:51 PM

Hi Guys

This may sound real stupid but I have the following issue which I am hoping you can help me with...

I have just sorted out a database in Excel 2008 (mac) but 1 of the fields which is for web addresses does not have the 'www.' before it..... Example:

google.com
youtube.com
hotmail.com
bbc.co.uk

Now, ..... I have just under 4000 fields with web addresses in that are like the above (don't have the 'www.' in front and so don't link to the web).
Does anyone know of a way for me to put the 'www.' in front of all of the addresses without having to spend ages doing it manually for each individual field?

Any help would be amazing

02-22-2010, 01:17 PM

I was thinking that using the Replace command could shorten the process, but I've concluded that it could leave you with a bigger mess. I did a test, and if you do a replace ****.com with www.****.com it will actually give you www.****.com which is not what you want. I was hoping it would leave the **** as the actual text and all four-letter domains would update. But, it won't. I'm not aware of any command that will add something to the beginning of designated cells. Maybe someone who knows about scripts could come up with something.

02-22-2010, 01:22 PM

Hey

Thank you very much for your suggestions, although it doesn't directly help with my issue, I do very much appreciate your time and effort in trying to help with my problem.

Mark

02-22-2010, 03:02 PM

The concatenate function should work for you.

1) insert an empty column to the right of your web addresses
2) in the first row of that new column enter { =concatenate("www.", A1) }
leave out the curly brackets and replace A1 with the cell reference to the first web address
3) Fill down the formula through all you records
4) Insert another new column to the right of the one with the formula, copy all the cells with the formula in it and paste values only into the new column.
5) remove the original web address column and the column with the formulas in it

02-22-2010, 05:34 PM

Very cool. I'm familiar with the term, but didn't know it was an Excel command. There's a little bit of jumping through the hoops there, but obviously less work than paste, paste, paste, paste.... that many times. Here's what Excel Help says about the command:

CONCATENATE
Joins several text strings into one text string.

Syntax
CONCATENATE (text1,text2,...)
Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

Remarks
The "&" operator can be used instead of CONCATENATE to join text items.

From that, I would think that there is no need for the quote marks around the www. and in fact, you wouldn't want them, because they would become part of the concatenated text string. I would think that the = is required, but it's curious that it is not noted in Help. Perhaps someone can confirm those things for you before you try it.

02-22-2010, 10:55 PM

You do need both the = and the "". if you dont wrap it in double quotes then it thinks its a cell reference, and you need the equal sign to let it know that it is a function.

02-23-2010, 08:51 AM

The "instructions" in Mac Help are poorly written then, I'd say. But, that's not unusual. Thanks for a great tip. I'll have to file that one away in my memory bank.