Skip to main content

I could not figure out why when doing an alphabetical sort, some records were coming in “at the bottom.” For example I’d have A to Z for records 1 to 100 followed by a second A to Z of records 101 to 200. I could see no extra spaces, or anything to indicate why this was happening. At long last I discovered that occasionally CSV imports (mine were exported from Zoho) have a glitch where hidden tabs can tag along.


The fix is a wee bit tedious, as you can’t just do a global replace (I did try that!), but it sure beats the alternative of retyping things (which was what I did for a while before getting determined enough to find the answer).


Steps:




  1. Open exported CSV file in your spreadsheet program. For each column, insert a blank column next to it on the right. In that column, add the formula =CLEAN(A1) Eor whatever cell is your first entry].




  2. Use the copy / drag function (copy, format down) to fill the entire second column. That column now has clean data with no extemporaneous tabs. It would be nice if you could stop there and just copy that column, but you have to do one more series of steps.




  3. Copy that column of clean data.




  4. Select the unclean data column entries and “paste value” over top of them.




  5. Delete the column with the formula and move on to the NEXT column in your database.




NOTE: Use =trim(CLEAN(A1) to additionally remove double spaces anywhere in the entry.


Thanks to Excel Jet for the video that explained how Excel can clean text. (Here’s the link to the video. It doesn’t mention Airtable, as that wasn’t their focus, but it does make importing TO Airtable a lot smoother knowing this! https://exceljet.net/lessons/how-to-clean-text-with-clean-and-trim)

I more often find doubled spaces than tabs when working with imported client data — but they’re just as nefarious, as Airtable’s CSS white-space setting causes multiple spaces to collapse down to a single space during display, making it impossible to locate duplicates without opening each and every imported field. However, there is a way to trim excess spaces within Airtable itself – and I just figured out how to make it work for tabs, as well.¹



  1. Import the CSV file into Airtable. For purposes of this discussion, I will call it {Import}.

  2. Create a new formula field; typically this is adjacent to the imported field you wish to clean. I shall call it {Fix}.

  3. For {Fix}'s formula, enter


    SUBSTITUTE(

        SUBSTITUTE(

            SUBSTITUTE(

                SUBSTITUTE(

                    {Import},'  ',' '

                    ),

                '  ',' '

                ),

            '  ',' '

            ),

         '▲',' '

        )

The first three SUBSTITUTE()s strip out repeated white spaces; from 2 to 8 space characters are now reduced to a single one.


To replace a tab character with a space, first select and mark the black triangle in the next-to-last line of code. Now, holding down the Alt key, enter 009 using the numeric keypad. When done, release ALT. The black triangle character is replaced by the Tab character; you will see the second closing quote leap forward, as if to the next tab stop. Once you save the changes, the formula will now match any tab characters embedded in {Import} and replace them with a space character.


Note: Again, these instructions assume use of the web interface from a Windows machine. There may be similar keystrokes valid for macOS, IOS, and Android; if so, I would greatly appreciate knowing them.


If all has gone well, {Import} and {Fix} should be visibly identical. You may now either copy the values in {Fix}, paste them into {Import}, and delete {Fix}, or you may convert {Fix} to a single-line text field and delete {Import}. Either way, the remaining field should be cleaned of extraneous spaces and tabs.


As I mentioned, I only just now discovered Airtable (under Windows, etc., etc.) will accept and match upon Alt-009; until then, it appeared your spreadsheet ‘pre-wash’ was the only viable solution. (It may still be for certain types of problematic exports or preprocessing.)




  1. When using the browser interface under Windows, at least. If anyone can provide the equivalent key sequences under macOS, IOS, or Android, I’d appreciate it.

Reply


Terms & Conditions