Excel formula compare two strings

1
votes

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

1
stringvbaexceltext
If you copy the Celle and paste AS values,is it true or false? Can it be that there are not visible signs within the Celle? - Max
Max, it still evaluates as False when I paste values. - Nick B
Can you copy paste the cell values into the question? - QHarr
There will be some hidden / not visible so signs. I can post code to eliminate Thema tomorrow. - Max
Try adding Clean to you function . TRIM(UPPER(CLEAN(C4))) - chris neilsen

1 Answers

0
votes

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