CODEWITHSUNDEEP
stringvbaexceltext
Nick B
Nick B

Reputation: 39

Excel formula compare two strings

screen shot of formula click here

I need to compare a product from a transaction file to a product in a lookup worksheet. The values appear to be equal but do not evaluate as equal. Been trying different approaches in VBA (StrComp, CStr, Trim(), adding a letter to the beginning and end of the strings for comparison) and Excel formulas to try and solve this.

In the photo, Cell C4 was copied from the transaction work book, and cell D4 was copied from the lookup workbook. The formula evaluates to False. If I just type this string value into two different cells and apply the formula, it evaluates to True, but when I copy the two cells from other workbooks (a transaction worksheet, and a lookup worksheet), the formula evaluates to false.

Any ideas?

Excel Formula: =IF(TRIM(UPPER(C4))=TRIM(UPPER(D4)),TRUE, FALSE)

A5314A A5314A

Upvotes: 1

Views: 597

Answers (1)

Nick B
Nick B

Reputation: 39

In Excel using the CLEAN function like Chris Neilsen suggested was the answer:

=IF(TRIM(UPPER(CLEAN(C4)))=TRIM(UPPER(CLEAN(D4))),TRUE, FALSE)

As Sam suggested in VBA using this function also worked and the StrComp method evaluated to 0:

a = "A" & Application.WorksheetFunction.Clean(Trim(CStr(Sheet1.Cells(i, 1).Value2))) & "A"`
b = "A" & Application.WorksheetFunction.Clean(Trim(CStr(Sheet2.Cells(i2, 1).Value2))) & "A"
If StrComp(a, b) = 0 Then

Upvotes: 0

Related Questions