New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
OS X - Apps and Games Discussion of applications and games available for Mac OS X.

Office 2008 (Excel) Problem


Post Reply New Thread Subscribe

 
Thread Tools
Mark McLean

 
Member Since: Oct 26, 2009
Posts: 25
Mark McLean is on a distinguished road

Mark McLean is offline
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
QUOTE Thanks
toMACsh

 
toMACsh's Avatar
 
Member Since: Jul 30, 2009
Location: Wisconsin
Posts: 5,497
toMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to all
Mac Specs: Mac Mini Core 2 Duo

toMACsh is offline
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.
QUOTE Thanks
Mark McLean

 
Member Since: Oct 26, 2009
Posts: 25
Mark McLean is on a distinguished road

Mark McLean is offline
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
QUOTE Thanks
jmullenberg

 
Member Since: Apr 27, 2008
Posts: 92
jmullenberg will become famous soon enough
Mac Specs: MBP 2.5 Intel Core 2 Duo, 2GB 667 MHz DDR2, 17" Hi-Res Display ; iPhone 3G 2.1

jmullenberg is offline
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

"Sure, I could compare PCs to Macs. But I make it a point to never argue about religion."
QUOTE Thanks
toMACsh

 
toMACsh's Avatar
 
Member Since: Jul 30, 2009
Location: Wisconsin
Posts: 5,497
toMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to all
Mac Specs: Mac Mini Core 2 Duo

toMACsh is offline
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:

Quote:
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.
QUOTE Thanks
jmullenberg

 
Member Since: Apr 27, 2008
Posts: 92
jmullenberg will become famous soon enough
Mac Specs: MBP 2.5 Intel Core 2 Duo, 2GB 667 MHz DDR2, 17" Hi-Res Display ; iPhone 3G 2.1

jmullenberg is offline
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.

"Sure, I could compare PCs to Macs. But I make it a point to never argue about religion."
QUOTE Thanks
toMACsh

 
toMACsh's Avatar
 
Member Since: Jul 30, 2009
Location: Wisconsin
Posts: 5,497
toMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to alltoMACsh is a name known to all
Mac Specs: Mac Mini Core 2 Duo

toMACsh is offline
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.
QUOTE Thanks

Post Reply New Thread Subscribe


« New iMac - Excel 2008 really slow | Text heavy browser »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
Microsoft Office 2008 fvsurfer OS X - Apps and Games 24 05-05-2009 06:55 PM
Office for Mac 2008 chunkychimpanC OS X - Apps and Games 4 03-16-2009 01:54 PM
having problems scanning with Office 2008 geofcoulson OS X - Apps and Games 0 02-18-2009 09:52 AM
Microsoft Office Mac 2008 Update help please koastal OS X - Apps and Games 0 10-24-2008 05:23 PM
Office Mac 2008 Excel Power User Question Maggie's Farm Switcher Hangout 0 04-03-2008 07:16 PM

All times are GMT -4. The time now is 02:37 PM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?