Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
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? -- Richard M. Perry |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
=IF(COUNTIF(B6,"Ho*")0,10,0)
"Richard" wrote: 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? -- Richard M. Perry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
Another way....
=IF(ISNUMBER(SEARCH("Ho",B6)),10,0) "Richard" wrote: 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? -- Richard M. Perry |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
Thanks. That will cut down on my spelling errors.
-- Richard M. Perry "Teethless mama" wrote: =IF(COUNTIF(B6,"Ho*")0,10,0) "Richard" wrote: 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? -- Richard M. Perry |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
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. Richard wrote: 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? -- Richard M. Perry -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
Just a warning...
That will find Ho in any position in the cell And another: =if(left(b6,2)="ho",10,0) Teethless mama wrote: Another way.... =IF(ISNUMBER(SEARCH("Ho",B6)),10,0) "Richard" wrote: 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? -- Richard M. Perry -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Spelling comparison
Thanks Dave. I'll check that out.
-- Richard M. Perry "Dave Peterson" wrote: 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. Richard wrote: 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? -- Richard M. Perry -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spelling Facility | New Users to Excel | |||
spelling errors in excel | Excel Discussion (Misc queries) | |||
Verify spelling in a vlookup formula | Excel Worksheet Functions | |||
spelling not working | Excel Discussion (Misc queries) | |||
spelling not working | Excel Discussion (Misc queries) |