If Statement to find word in Column and Make value 0

[Evan_McMahon]

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?

[Julian_E_Post]

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

[Evan_McMahon]

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?

[Julian_E_Post]

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

[Evan_McMahon]

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)

[Julian_E_Post]

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)

[Evan_McMahon]

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

[Julian_E_Post]

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