Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Hidden difference between values that appear the same in two columns

Topic Labels: Formulas
Solved
Jump to Solution
314 5
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

1 Solution

Accepted Solutions
PeterB
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

See Solution in Thread

5 Replies 5

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.

PeterB
5 - Automation Enthusiast
5 - Automation Enthusiast

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.