Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: If Statement to find word in Column and Make value 0

Solved
Jump to Solution
1947 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Evan_McMahon
4 - Data Explorer
4 - Data Explorer

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?

1 Solution

Accepted Solutions
Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

7 Replies 7
Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

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

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)

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

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