Business Computing Tips

By K&K Fainges kfainges@...

Ever had to work with a spreadsheet where others have entered the
information? It can be a real problem at times. Take the example
below. Here St Catherines is entered four different ways. That is
without spelling mistakes. In a large data group, something like that
could be entered seven different ways or more.

School Name
Ambrose
Churchies
Saint Catherines
Saint Catherine's
st catherines
St Phill's
St. Catherines

The problem compounds when other schools appear in between the
different spellings. It makes it very difficult to sort out all the
examples of just one school. If you did it just by alphabetical
order, you would miss the last entry.

Enter Excel's Auto Filter function.

Click on the top of the column, go to DATA => FILTER => AUTO FILTER.
A small down arrow will appear at the top of the column. Click on
that arrow. A list will appear. The third option is custom. Click on
that.

A box appears with four white boxes. On the top left box it will say
equals. If you click the down arrow here and scroll down to the
bottom, you will see the word contains. Click on that. If you type
cath in the box next to that, the list becomes:

School Name
Saint Catherines
Saint Catherine's
st catherines
St. Catherines

You can go one step further and use EDIT=> FIND=> REPLACE to change
all the alternate spellings to the one you decide to use. To replace
all the different spellings at once, try typing S*Cath* in the find
box. The * symbol stands for any character.

**********************************************************************

Please feel free to pass it on to your friends, just let them know I
wrote it.

Karen Fainges holds a Bachelor of Business, and a Grad. Cert of
Vocational and Educational Training. All this is nice but it's the 14
years of having to make sales or starve that makes her think she has
really learnt what does and doesn't work. A tutor for all ages, she
specialises in helping people get started on the long road to
technology.

"It has to be practical, it has to be cheap, and it has to work."