Spelling comparison

[Guest]

Hi everyone - I run a track meet for twelve grade schools. My Excel score
sheet involves recording the winning student's name and school in one
section. In another section I compare the school name and award points for
that event if there is an exact match. The problem is if I misspell the
school name, the sheet fails to award any points. My function used (for
example, if the school name is "Hoover") is:
"=if (B6="Hoover", 10,0).
Is there a way to compare names with a wildcard so that a misspelling
doesn't doom my calculation. When typing fast, I might inadvertently type in
"Hover" instead of "Hoover." I would like to be able to use a wildcard such
as "Ho*" but can't seem to get that to work. Any thoughts?

 

[Guest]

=IF(COUNTIF(B6,"Ho*")>0,10,0)

 

[Guest]

Another way....

=IF(ISNUMBER(SEARCH("Ho",B6)),10,0)

 

[Guest]

Thanks. That will cut down on my spelling errors.

 

[Dave Peterson]

If you add a list of valid names somewhere, you could use Data|Validation. Then
you could choose from that list--no typing.

Debra Dalgleish has lots of notes about Data|Validation:
http://www.contextures.com/xlDataVal01.html

===
Another option (if your names are in a contiguous column):

Tools|Options|Edit Tab
check the "Enable Autocomplete for cell values"

This will look at the cells above and below to give you a list that matches what
you've typed. Ho could show you Hoover.

It's kind of like rightclicking on the cell and choosing "pick from
list"--another option!

And if you're really lazy <bg>, you could use tools|autocorrect options.

Have $$ho replaced with Hoover
(some unique string replaced by the correct name)

You'll have to add all the names you want corrected, though.

 

[Dave Peterson]

Just a warning...
That will find Ho in any position in the cell

And another:
=if(left(b6,2)="ho",10,0)

 

[Guest]

Thanks Dave. I'll check that out.