Skip to main content
Solved

If Statement to find word in Column and Make value 0

  • December 12, 2020
  • 7 replies
  • 64 views
E
Forum|alt.badge.img+3

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?

Best answer by 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")

7 replies

Julian_E_Post
Forum|alt.badge.img+13
  • Julian_E_Post
  • Inspiring
  • Answer
  • December 12, 2020

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

Justin_Barrett
E
Forum|alt.badge.img+3
  • Evan_McMahon
  • Author
  • Participating Frequently
  • December 13, 2020

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?

Julian_E_Post
Forum|alt.badge.img+13

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

E
Forum|alt.badge.img+3
  • Evan_McMahon
  • Author
  • Participating Frequently
  • December 13, 2020

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)

Julian_E_Post
Forum|alt.badge.img+13

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)

kuovonne
Justin_Barrett
E
Forum|alt.badge.img+3
  • Evan_McMahon
  • Author
  • Participating Frequently
  • December 13, 2020

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!!

Julian_E_Post
Forum|alt.badge.img+13

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:

E
Terms & ConditionsAccessibility statement