Strange Behaviour Using SUBSTITUTE()

[Josh_Haywood]

Hi!

I’m trying to create a formula that extracts a keynumber from a string of text to add to a field in Airtable.

The input is in some form like this:

NB - 1720ENTHXXXXXAR - TTQ 1720 NEWSBREAK THURSDAY - 0:15

And the output should be:

1720ENTHXXXXXAR

The problem I have is that the source doesn’t use consistent formatting, and the original string has an inconsistent amount of blank space characters all throughout, which I’m getting rid of using SUBSTITUTE(), and then using the MID() formula to grab the key number from the text with the spaces removed.

This is my formula:

MID(SUBSTITUTE({Input}," “,”"),4,15)

However, there appears to be some inputs that aren’t fixed with the SUBSTITUTE() function, and as a result am getting an incomplete key. This, weirdly, is fixed by adding, removing or modifying a character anywhere in the original string.

Here’s a sample base if you want to check the data.

There seems to be some carriage returns in the original string, but Airtable should be converting those to white space, and doesn’t explain how adding a character could fix the output.

If there is something else I can substitute, or even a better way to grab the key number that wouldn’t face this issue, I’d love to hear about it!

[Justin_Barrett]

It took a few fiddles to figure it out, but it was your comment below that gave the clue to the solution:

Josh_Haywood:

There seems to be some carriage returns in the original string, but Airtable should be converting those to white space, and doesn’t explain how adding a character could fix the output.

You’re right, it doesn’t quite explain how adding a character fixes the output. However, you can add an extra SUBSTITUTE() layer that searches specifically for carriage returns, and the extraction works:

MID(SUBSTITUTE(SUBSTITUTE({Input}," ",""),"\n",""),4,15)

[Josh_Haywood]

Fantastic! Thanks, Justin.

I didn’t realise you could use those kind of codes in formulas in Airtable, this has opened up a whole new world.

Cheers :slightly_smiling_face:

[W_Vann_Hall]

Josh_Haywood:

There seems to be some carriage returns in the original string, but Airtable should be converting those to white space,

Airtable doesn’t convert newlines to whitespace; instead, depending on how you have line-height configured in your grid view, it sets the css white-space property to (IIRC) either ‘normal’ or ‘pre’. This causes the browser to collapse newlines and other white-space characters to a single white-space character, but it has no effect on the underlying data…

I’m not sure why modifying a character in the original string resolved your problem, but @Justin_Barrett’s solution of SUBSTITUTING() out the newlines is probably the best approach.

---

Edit: Oh, figured out why modifying the original string fixes the issue. I had assumed you meant adding text via formula — e.g., {Input}&' - new text'. You meant doing so by selecting the text field, hitting return to edit it, and typing in the additional text. I’m not sure if this is documented anywhere — and I’d never thought of testing it before — but evidently when you edit a single-line text field, Airtable does convert and collapse white-space characters. (It does this regardless how line-height is set.)

You can see this by setting line-height to something other than ‘short’ on a single-line text field containing newlines. Initially, the line breaks are maintained. If you select the field and press Enter, the line breaks and sequences of multiple white-space characters collapse into single white-space characters. If you select another field without modifying the selected text, the data revert to the original configuration of line breaks and white-space, but if you make any changes to the text, the modified entry is saved with collapsed white-space. (Repeating the experiment using a long text field results in line breaks and white-space being maintained.)

Just FYI, in case someone later on gets tripped up by this…