If Statement to find word in Column and Make value 0

Sorry in advance - I am a very green user to Airtable and Databases in general.

I would like to make a formula that can scan a record for a word and then automatically make the value 0.

Example:

Line Item ------------Unit ----------- Rate---------Total Cost
Invoice - 0011-------1.00------------$100----------$100
Bid - 0100------------2.00------------$500----------$0

In this case I am using:

Rate*Unit to reach the total cost. This will mean that the “Line Item” Bid-0100 will pop up with a total cost of $500, but I need it to show 0 as it hasn’t actually cost me anything. Is this possible?

Hi Evan,
Welcome to the forum. You should be able to achieve this using Find() or Search():

IF(FIND("Bid",{Line Item}),"value if true","value if false")

Keep in mind that the function is case sensitive. If you need to find upper and lower-case versions of “Bid”, use this

IF(FIND("bid",LOWER({Line Item})),"value if true","value if false")

1 Like

This is great. The only issue I am running into is that the solution isn’t popping up with a dollar amount, just a numeric value. Anyway to fix that?

Right click on the field and choose “Customize field type”, then choose the formatting tab. It should give you the option to display as currency

Thanks Julian,

Unfortunately Formatting isn’t an option for some reason.

“Your result type is not a number or a date. Formatting options are currently only available if your result type is a number or a date.”

This is the formula I am using:

IF(FIND(“Bid”,{Line Item}),“0”, Unit*Rate)

Remove the quotation marks from the “0”. Since one result (the “0”) is a string, Airtable treats the whole field that way. Try this:

IF(FIND(“Bid”,{Line Item}),0, Unit*Rate)

2 Likes

This worked beautifully! You’re a life saver!!

Glad to hear! If you don’t mind, mark the correct answer as “Solution” :slight_smile:

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.