Create a code that doesn't differentiate between capital and small letters

Fredrik1987

Board Regular
Joined
Nov 5, 2015
Messages
69
Hi!

I'm wondering if there's a way to create a simple VBA code that interprent a name, indifferently it starts with a capital letter or not. I've been searching around a little, and haven't really found anything.

For instance:
Column A consist of names (Hans, Gretel etc.). Where each name correspons to a value in column B (1, 2... etc.)
What I want the script to understand, is that if I insert "hans" instead of "Hans", the meaning are the same.
Also, is there a way to include misspelling (e.g. ahns instad of Hans)

Column AColumn B
Hans1
hans1
ahns1

<tbody>
</tbody>

This is just a simplified example of a large script, I think I read somewhere that it is possible to script in such a way that VBA only looks for the letters in a word, indepent of their arragement?
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Yes and no... you can use the "lower" or "upper" function to standardize your entries so that hans = Hans. As for misspellings, you'd have to code those in. Easy enough for Hans, which doesn't have that many permutations, but more challenging with Elizabeth, Meriweather, and other longer names.
 
Upvote 0
You can also put this at the top of your module (above everything else)

Option Compare Text

That will make your code not case sensitive (ALL code in that module).


As far as misspelling, that's very tricky becase techincally speaking, Excel (or a computer in general) can't actually spell.
It can really only compare an entry against a list of known words.
So it has no way to know that ahns isn't actually a real name.

If you have a list of known names in their correct spellings, then it could be doable with some sort of Match function.
 
Upvote 0
to follow up without allowing for spelling mistakes two option for checking on with exact match and one that is not case sensative

i have the name to compare in A1 my example i have put Cat in A4 cat in A5 and Tac in A6

for exact match i used

Code:
=EXACT(A4,A1)

for indirect i used

Code:
=A4=A1

not a solution but may offer ideas to help


another idea is

http://www.excel-university.com/perform-approximate-match-and-fuzzy-lookup-in-excel/
 
Last edited:
Upvote 0
EDITED
misread post number


my ideas will not help as i misunderstood the question or requirements .. i am thinking some sort of VLookup or HLookup will be solution combined with an IF

but to cater for the spelling errors i can only see creating a table with synonyms of the primary item so lookup checks options and returns same value.
 
Upvote 0
Hi!

Jonmo1, that was what I was afraid of :)
It isn't really that important, but I'll post a solution if I find a quick fix.
For different reasons I can't make the whole script case sensitive, but I'll try to implement it.

But thank you so much for your help!
 
Upvote 0
You could have code that determines if two words contain the same letters and that would pick up your example of "Hans" and "ahns". :)

The question is "Do you really want that?" as it would also mean two completely different names like "Ronaldo" and "Orlando" were seen as the same. :cry:
 
Upvote 0
Well, as it stands now that won't be a problem. None of the names are even closely similiar :)
But for now, I think Johnmo1's solution would suffice, that being said, the code you're refering to would be immensely useful for later use:)
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top