Results 1 to 2 of 2
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pattern Match (Excel XP)

    Anyone got a neat bit of code (or function) that performs a match on two strings?

    I want to get a YES to things like "k mart" = "k-mart"
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pattern Match (Excel XP)

    Add this function into a module.

    <pre>Function UStrip(sWord as string)
    Dim x As Long
    Dim sCharacter As String
    sWord = UCase(sWord)
    UStrip = ""
    For x = 1 To Len(sWord)
    sCharacter = Mid(sWord, x, 1)
    If Asc(sCharacter) >= 65 And _
    Asc(sCharacter) <= 90 Then
    UStrip = UStrip & sCharacter
    ElseIf Asc(sCharacter) >= 48 And _
    Asc(sCharacter) <= 57 Then
    UStrip = UStrip & sCharacter
    End If
    Next x
    End Function</pre>


    Then you can compare (eg) A1 and A2 with:
    =if(ustrip(a1)=ustrip(a2), "yes", "no")

    You compare the uppercase stripped versions (only numbers and letters remain), so k-mart, k_mart, kmart, k*mart, etc and upper/lowercase combos re converted to KMART and then compared.

    Steve

Posting Permissions

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