Pages

Friday 1 February 2002

Newsletter Issue 39, February 2002


Sam Young Newsletter

Issue 39, February 2002
Hi guys,
Check out the Excel Tidy-up Tip below for a very easy to follow way to get rid of extra spaces in spreadsheets. I also have a bit of a hot software tip for you. Check out the MS add on Reminders in Outlook
Don't forget, if you want to be taken off my mailing list, click here to send me a reply e-mail and I will remove your name.

Dirty-Flash Excel Tidy-up Tip

Ever got a spreadsheet (aka Address database) from someone else that has extra spaces in it? 
So when you mail merge you get extra spaces at the ends of the lines or between concatenated words (like "first name", "space", "space", "last name"?). Extra spaces at the end of a text entry or between words in a cell will create headaches. And if a text entry contains two or more spaces between words, that extra white space looks sloppy and unprofessional. 
However, getting rid of the double spaces is a breeze;
  • Click the grey cell at the intersection of row 1 and Column A (selects the whole sheet - or Ctrl & A)
  • Go to the Edit menu. Select Replace (or Ctrl & H)
  • Key a double space into the "Find What" field
  • Key a single space into the "Replace With" field
  • Click the "Replace All" button
However, Find & Replace doesn't get rid of those pesky trailing spaces. 
So another approach that will clean up ALL your text in one fell swoop is the TRIM function. This function is =TRIM(source_cell). It removes all leading and trailing spaces, as well as all but one space between words. 
To use; 
  1. Insert a blank column beside the one that contains extra spaces (eg data starts in A, enter a new column B)
  2. Enter the formula =TRIM(A2) in B2 
  3. Use the fill handle to drag it down the length of your data
  4. Highlight the whole of column B (click on the column header). Click the copy tool (or Ctrl & C)
  5. Highlight the whole of column A. Select Edit | Paste Special, and choose "Values". This will convert the formula to literal strings and paste it over the top of your original data
  6. Delete column B
  7. Repeat 1-6 beside all your data columns 
NB this is the simplest (the Dirty-Flash) way of doing this. A cleaner way would be to create another sheet in the workbook, pick up all the data using the same formula but linked to the other sheet fill handle down & across then copy the whole new sheet back over itself, leaving the original data intact in case you wanted to verify the data later.
And if any of you are wondering about the meaning of "Dirty-Flash"; it's when you have go out, run out of time/energy, so change your clothes, have a shower from a deodorant bottle and slap on a quick face or shave (depending on personal requirements!)... Thanks to Helen Telford for that.

Reminders in Outlook

I think nearly all of us rely on hearing that MS Outlook "ding-dong", followed by a little Outlook reminder blurb with the reminder details (providing you put a sensible explanation in your task, calendar or email reminder when you set it up, of course!). 
Well, if you've had a play with Outlook, you'll know that as well as Calendar appointments you can also set up reminders on Inbox email messages , Task List tasks, or Contacts folder contacts. You right-click on the message/appointment/task/contact, select "Flag for Follow Up" and chuck in a date & a time. And then when it rolls around, the reminder will come up. 
Bit of a limitation with Outlook though; it only lets you set up reminders in your default Inbox, Calendar, Tasks or Contacts folders. If you want to set reminders in any other folder, you can't get there from here. 
That's a real pain in the neck if you - like me - have your own custom folders. 
However, Ken Slovak (a real whizzy-do software analyst & developer) has released a new Outlook tool called Extended Reminders which lets you set up reminders on any appointment, message, task or contact, except for Deleted Items and Outbox. 
It even works on Sent Items, the one place where I commonly tuck away messages with reminders. 
Extended Reminders works with Outlook 2000 and 2002 (and on either Exchange mailboxes or .pst files). And the cost is minimal (USD$15 shareware). 
Check it out.

The Ins & Outs of ASDL

Wanting to surf the Net and talk on the phone at the same time? Trouble trying to receive faxes as you are always on the net?
If you are looking to move up to a high speed ADSL Internet connection like Xtra JetStart, then there are a number of costs & pitfalls that you need to be aware of before you commit yourself to the service.
Aside from the increased line rental of $70 per month and a $99 initial start up charge, you are looking at $800 (excluding GST) to get a good external modem, getting it professionally installed and testing the system. There are all sorts of glitches that can cause you a lot of trouble with JetStart, so you need to make sure that you get professional advice. Well, it's either that or just tear your hair out first & save yourself the frustration.
For the Xtra promo blurb on it all, you can check out the Xtra Help Channel FAQs at http://xtra.co.nz/help/0,,5739-907829,00.html 

TLAs for SMEs

Here are this newsletter's TLAs for you;
  • ASDL, Asymmetric Subscriber Digital Line. Provides continuously-available, "always on" connection, transmits digital info at high bandwidth on existing phone lines 
  • KDE, K Desktop Environment. Open source graphical desktop environment for UNIX workstations 
  • POTS, Plain Old Telephone Service. Fairly self explanatory!

Please feel free to email me with any TLAs that you want to get the bottom (meaning!) of.

Short & Hot Keys... and now tips
All the Function keys for you - this time it's all you can do with Ctrl &/or Alt & F3;
  • Word "Create Auto Text"     ALT & F3
  • Word "Spike"     CTRL & F3
  • Excel "Define a name"     CTRL & F3 
  • Excel "Create names from row and column labels"     CTRL & SHIFT & F3 
  • Publisher "Find"     CTRL & F or F3 
Hot Linx
If you think that Xmas and Summer might have hit the waistline a bit too hard, check out your BMI (body mass index) at the international weight watchers site at http://www3.weightwatchers.com/international/nz/about/bmi_index.html 
And for a good article on the Kmart bankruptcy, check out the San Francisco Chronicle's article or Kmart at http://www.bluelight.com/home/index.jsp then click the Corporate News link
Want to know how to get rid of people who hang around on the phone too long? Then check out this TechRepublic article
Tired of available screen savers? You can use this freeware to automatically resize, add music to and integrate your .bmp, .jpg, and .gif files into your own s/svr slide show at http://www.pcworld.com/downloads/file_description/0,fid,8210,tk,hsx,00.asp 

                                Catch you again soon!! E-mail your suggestions to me here