Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 22, 2021 02:01 AM
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?
Solved! Go to Solution.
Aug 22, 2021 07:31 AM
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.
Aug 22, 2021 05:33 AM
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?
Aug 22, 2021 05:37 AM
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.
Aug 22, 2021 06:13 AM
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.
Aug 22, 2021 06:58 AM
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.
Aug 22, 2021 07:31 AM
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.