Hidden difference between values that appear the same in two columns

I have a table containing a PostCode column, and Address columns 1 to 6, all of type “single line text”. I wish to know which of the columns contains the PostCode and will use SWITCH(PostCode,Address1,1,Address2,2 …). However even when I can see which column contains the PostCode, let’s say Address3, a formula of PostCode = Address3 is returning false, when it should be true as they appear equal. Adding LEN() to both sides of the formula confirms they are the same expected length. The values I see in both columns appear identical. This behaviour is not the same in a table created with just these 2 columns and the values manually input. What differences in the values could there be that is not visible and how might the difference be eliminated?

Hi Peter. Let me see if I have this right. You have a PostCode column that’s going to be a formula which attempts to extract the postcode contained in 1 of 6 other address columns that can randomly contain any part of the address?

Can you post a screenshot of your table?

No the PostCode column is populated, not a formula. The issue is mostly as in the heading i.e. there’s a hidden difference in values.

I see. I tried to recreate your example and couldn’t make any two text strings that appear visually equivalent to satisfy the equivalency check (=) or LEN(). How do the values get into the table? Have you tried using T() on the two postcode fields in the equivalency check? How are you going to use the resulting value from the switch statement?

Sorry for so many questions.

Thanks for your questions. Values were imported from CSV files. T() produces same result i.e. that values are not equal. I’ll try a re-import.

Exported in preparation for a re-import and found the same issue in Excel. So used a Hex editor and sure enough there was a hidden character. So problem solved and not an Airtable issue. Thanks for responses.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.