Skip to main content

I regularly pull reports from multiple systems that include product IDs. These IDs are always a series of letters followed by numbers (e.g., ABC 123, WXYZ 9876). The number of letters can vary between 3 and 6 characters and the same goes for the numbers.


The problem I’m having is that in one system, there is no space within the ID (e.g., ABC123) while in the other system, there is always a space between the letters and numbers (e.g., ABC 123). Since the number of characters can vary, I’m having trouble finding a formula that basically looks at the cell and adds a space when it finds a number. Any help is appreciated!


Update:

If it helps, this formula is what does what I’m looking for in Excel

=TRIM(REPLACE(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&“1234567890”)),0," "))


TRIM, REPLACE, and FIND all exist in Airtable but I’m not sure how to use FIND to search for any combination of items (e.g., 1 OR 2 OR 3…) like you can in the above Excel formula.

Hi @Jonathan_Gunnell1, I’m sure you’ve thought of this, but just in case…


Generally, spaces in an ID field are not great. If you just have two systems and one provides IDs with a space and one without, could you simply remove the space from the system that gives a space, rather than trying to add a space to the system ID that doesn’t? So much easier to achieve with an AT formula:


SUBSTITUTE(string, old_text, new_text, windex])


JB

Hi @Jonathan_Gunnell1, I’m sure you’ve thought of this, but just in case…


Generally, spaces in an ID field are not great. If you just have two systems and one provides IDs with a space and one without, could you simply remove the space from the system that gives a space, rather than trying to add a space to the system ID that doesn’t? So much easier to achieve with an AT formula:


SUBSTITUTE(string, old_text, new_text, windex])


JB



This is a great suggestion. Unfortunately, I had initially tried this approach but then I ran into issues when I had to pull data out of Airtable and upload .csv files into another system that uses the spaces. When that happens, I just pull it into Excel and use the formula mentioned above first, but I would love to find a way to continue using the spaces within Airtable to eliminate that step.


This is a great suggestion. Unfortunately, I had initially tried this approach but then I ran into issues when I had to pull data out of Airtable and upload .csv files into another system that uses the spaces. When that happens, I just pull it into Excel and use the formula mentioned above first, but I would love to find a way to continue using the spaces within Airtable to eliminate that step.


Here’s something that I got to work:


LEFT(ID, (LEN(ID) - LEN("" & VALUE(ID)))) & " " & RIGHT(ID, LEN("" & VALUE(ID)))

Running this on non-spaced IDs will add a space before the number portion. If you need to mix and match spaced and non-spaced IDs and have them all come out with spaces, this slight tweak will work:


IF(

    ID,

    TRIM(

        LEFT(

            ID,

            LEN(ID) - LEN("" & VALUE(ID))

        )

    ) & " " &

    RIGHT(

        ID,

        LEN("" & VALUE(ID))

    )

)

Here’s something that I got to work:


LEFT(ID, (LEN(ID) - LEN("" & VALUE(ID)))) & " " & RIGHT(ID, LEN("" & VALUE(ID)))

Running this on non-spaced IDs will add a space before the number portion. If you need to mix and match spaced and non-spaced IDs and have them all come out with spaces, this slight tweak will work:


IF(

    ID,

    TRIM(

        LEFT(

            ID,

            LEN(ID) - LEN("" & VALUE(ID))

        )

    ) & " " &

    RIGHT(

        ID,

        LEN("" & VALUE(ID))

    )

)



Excellent. Thank you!

Reply


Terms & Conditions