#ERROR message when LEFT or extracting text formulas used

[Jesper_Holmstro]

Hi guys, have been sitting with this a couple rounds now and can’t get it right. Im extracting a telephone number from a column in Airtable, but when there is no phone number or the word message I get the famous #ERROR. How can I get Airtable to just show it as blank?
My formula looks like this:
IF({Telefon 1},(LEFT({Telefon 1}, FIND("Message", {Telefon 1}) -1)),BLANK())
Any advice is welcome
Regards
Jesper

[Mohamed_Swella1]

Hi @Jesper_Holmstrom

Im actually using your formula and im not getting #Error where there is no “Message”

Not sure what type of field is {Telefon 1} though.

I would however try to do this:

IF(AND({Telefon 1},FIND("Message", {Telefon 1})>0),(LEFT({Telefon 1}, FIND("Message", {Telefon 1}) -1)),BLANK())

[Jesper_Holmstro]

Hi Mohamed
I can’t get this to work, I have the same problem with this formula that extracts text, but get the error message when the word Interest doesn’t exist:
IF({Meddelande 1},REGEX_EXTRACT({Meddelande 1},"(?:interest:)([a-zA-Z\\a-zA-Z\\a-zA-Z]*)"),BLANK())

[Jesper_Holmstro]

The “telefon 1” field is an formula field and “meddelande 1” is just long text field.

[Mohamed_Swella1]

Hi @Jesper_Holmstrom

So did the first formula work?

For the second formula, I dont experience with REGEX sorry

[Jesper_Holmstro]

Hi @Mohamed_Swellam
No sorry but it didn’t work…cant understand why,

[Jesper_Holmstro]

@Mohamed_Swellam I now got your formula to work but It seems that the problem is further down with this formula:
IF({Meddelande 1},REGEX_EXTRACT({Meddelande 1},"(?:interest:)([a-zA-Z\\a-zA-Z\\a-zA-Z]*)"),BLANK())
If there is no “interest:” in the field I want the formula field to be blank. Anyone got an idea?
Thanks
Jesper

[augmented · Accepted solution]

Wrap your formula in…

IF(FIND('interest:', {Meddelande 1}), *<insert your formula here>* )

[Jesper_Holmstro]

Yup that did the trick!
Thanks @augmented

[Justin_Barrett]

As a side note, you can omit the BLANK() function at the end. By default if there’s no third argument in an IF() function, it returns nothing, which will automatically leave the field empty.