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:
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:
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.
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!