Help

Re: #ERROR message when LEFT or extracting text formulas used

Solved
Jump to Solution
1327 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions

Wrap your formula in…

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

See Solution in Thread

10 Replies 10

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
6 - Interface Innovator
6 - Interface Innovator

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())

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

Hi @Jesper_Holmstrom

So did the first formula work?

For the second formula, I dont experience with REGEX sorry

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

Jesper_Holmstro
6 - Interface Innovator
6 - Interface Innovator

@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

Wrap your formula in…

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

Yup that did the trick!
Thanks @augmented

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.

OK, thanks @Justin_Barrett, I didn’t know that!