Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Create a code that doesn't differentiate between capital and small letters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2015
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 A Column B
    Hans 1
    hans 1
    ahns 1

    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 by Fredrik1987; Nov 13th, 2015 at 08:50 AM.

  2. #2
    Board Regular Gingertrees's Avatar
    Join Date
    Sep 2009
    Location
    Michigan, USA
    Posts
    697
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,059
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

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

    you could look at converting the letter to numbers adding them up to a total and then comparing this total to equal the value of the name

    am not a coder but this explains my idea better

    http://www.mrexcel.com/forum/excel-q...b%3D2-etc.html
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  5. #5
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

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

    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/perf...okup-in-excel/
    Last edited by AkaTrouble; Nov 13th, 2015 at 10:32 AM.
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  6. #6
    Board Regular West Man's Avatar
    Join Date
    Mar 2006
    Location
    Nebraska
    Posts
    1,175
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I don't believe the idea in post 4 will work, as ad would equal bc

  7. #7
    Board Regular
    Join Date
    Dec 2014
    Location
    little ole England
    Posts
    1,542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

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

    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.
    ◄ŦяμвŁ►
    By Name and By Nature

    i know nothing but i know everything about it
    please take everything i type with a pinch of salt i am only learning this stuff as i go along. Please make a post helping someone else
    Want to post a small screen shot? Try MrExcel HTML Maker
    How To Install MrExcel HTML Maker: https://www.youtube.com/watch?v=Jycv...ature=youtu.be

  8. #8
    Board Regular
    Join Date
    Nov 2015
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,005
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

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

    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Nov 2015
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •