UDF- Like worksheet function

Question

Howdee all.

I know that this has been discussed before, but I can't find anything under the search tool.

I know that VBA has code that will allow an instr search, to do a comparison for strings, character by character. Akin to LIKE.

And In times past, I've read discussions on this, but I can't find anything on it-- I'm guessing because my syntax in my search is wrong.

Has anyone worked out a UDF yet that will allow for string comparisons on a worksheet? I.e., If string A matches, or is similar enough to String B, give a true response.

Thank you for your helps.

Best.

Answer 1

John Doe

vs

John   Doe (note the 2 extra spaces between the first and last name)

Okay, this one is easy... just use apply the WorksheetFunction.Trim function to each string to squeeze out the extra spaces (assuming they are pure spaces and not ASCII 160 characters... the non-breaking space websites use). So...

If WorksheetFunction.Trim(Range("A1").Value) = WorksheetFunction.Trim(Range("B1").Value) Then

If the spaces could be ASCII 160 characters, then use the VBA Replace function to get rid of them...

If WorksheetFunction.Trim(Replace(Range("A1").Value, Chr(160), " ")) = WorksheetFunction.Trim(Replace(Range("B1").Value, Chr(160), " ")) Then

John Do

vs

Jon Do

This is a little harder problem because of the sheer number of variations possible depending on the text involved. In this case, perhaps a SoundEx function might be useful. See this link for more details...

http://www.j-walk.com/ss/excel/tips/tip77.htm

You can combine function calls to the SoundEx function with the WorksheetFunction.Trim and Replace codes I showed above.

In this specific case, I have a 10 digit number

010-482-21

for some reason in comparing that to another, which appears exactly the same, the IF function fails, and returns a false.

=IF(a1=b1,"ok", b1)

a1 = 010-482-21

b1 = 010-482-21 (with some indeterminable difference from the contents of a1)

I don't know what it is, but it returns a false. I've checked the data type, the spacing of blanks in the cell, verified that the digits, and cell's contents actually match, and nothing that I've done can find it.

In this specific case, and without being able to see your actual worksheet, I would check to make sure all the things that look like zeroes are, in fact, zeroes and not the upper case letter "O". And I would check to make sure all the ones are, in fact, the number one and not the lower case case letter "L". There is also the possibility that you have a trailing blank space or trailing ASCII 160 character attached to one of those values, but my first suggestion above should take care of that problem.

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

Thank you Rick.

I'll review John's Soundex function, and go from there.

I do have a replace, and a trim macro, to resolve the trim matters, or replace items.

Again-- thank you for yor help!

Answer 3

Matching is no problem, of course, but can you describe "similar enough" for us so we will know what you have in mind for this? Also, are we talking about matching (or nearly matching) the entire content of a cell or can what we are trying to match be part of the cells content with other text around it? Also, if not covered by "similar enough", is there a difference between upper and lower case letters... "Yes" versus "YES" for example?

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

hi Rick, Thank you for your time.

Ok...

John Doe

vs

John   Doe (note the 2 extra spaces between the first and last name)

OR

John Do

vs

Jon Do

In this specific case, I have a 10 digit number

010-482-21

for some reason in comparing that to another, which appears exactly the same, the IF function fails, and returns a false.

=IF(a1=b1,"ok", b1)

a1 = 010-482-21

b1 = 010-482-21 (with some indeterminable difference from the contents of a1)

I don't know what it is, but it returns a false. I've checked the data type, the spacing of blanks in the cell, verified that the digits, and cell's contents actually match, and nothing that I've done can find it.

Answer 4

Matching is no problem, of course, but can you describe "similar enough" for us so we will know what you have in mind for this? Also, are we talking about matching (or nearly matching) the entire content of a cell or can what we are trying to match be part of the cells content with other text around it? Also, if not covered by "similar enough", is there a difference between upper and lower case letters... "Yes" versus "YES" for example?

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.