check each letter and remove

str1: "CLIO EXTREME TOM TOM (75)"
str2: "CLIO EXTREME TOMTOM (75)"

I would like to traverse each character in str1 if i find it in str2 remove that character from str2

if what remains is a space then that remains the only difference in the strings so flag it as false

this will allow me to ignore strings that have done this TOM TOM vs TOMTOM and by doing this character by character it doesnt matter if they rearranged the words.

I am working in vba

PeterBaileyUkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Have you looked into Soundex algorithms (i.e. "fuzzy matches") for these operations? Soundex algorithms can determine if one set of characters very closely mimics another, which is (basically) what you're trying to do.

http://allenbrowne.com/vba-Soundex.html
0
 
JezWaltersCommented:
Wouldn't removing all spaces from both strings, and then comparing the strings give you the same result?
0
 
PeterBaileyUkAuthor Commented:
botj interesting yes to Jez and i will look at the fuzzy logic
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
JezWaltersConnect With a Mentor Commented:
You can remove spaces like this:
Option Explicit        ' Force explicit variable declaration
Option Compare Binary  ' Perform case-sensitive comparisons

Public Function RemoveSpace(pvarStrOption Explicit        ' Force explicit variable declaration
Option Compare Binary  ' Perform case-sensitive comparisons

Public Function RemoveSpace(pvarString As Variant) As String

    ' Declare variables
    Dim lngCharacter As Long

    ' Process string
    RemoveSpace = Nz(pvarString)  ' Cope with Null
    For lngCharacter = 1 To Len(RemoveSpace)
        If Mid(RemoveSpace, lngCharacter, 1) = " " Then
            RemoveSpace = Left(RemoveSpace, lngCharacter - 1) & Mid(RemoveSpace, lngCharacter + 1)
        End If
    Next

End Function

Open in new window

0
 
JezWaltersCommented:
Once you've created this function, you can use it in a query like this:

SELECT *, RemoveSpace(YourField1) <> RemoveSpace(YourField2) As Different
FROM YourTable
0
 
JezWaltersCommented:
How do you want to handle different capitalisation between the two strings?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.