Help

If statements categorizing products from first # in serial #

Topic Labels: Automations Formulas
1768 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Helen_Harrell
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello!

I am trying to create if statements to categorize products.  For example: if the product id is 02568, it will be category a; if its 12487 it will be category b; if 25777 it will be category c, an so forth.  However, each product id number is not the same.  So, category A would have serial numbers like 03577 as well as 06554 and so forth.  Each category starts with the same first number.  First problem is trying to identify in airtable only the first number to categorize.  I tried doing this: 

if({Product ID #}=0, "A", IF({Product ID #}=1, "B", IF({Product ID #}=2, "C"))))
However, airtable says "Sorry, there was a problem saving this field. Invalid formula. Please check your formula text."  Please help!
 
My second issue that I'm trying to fix is for airtable is to not drop the 0 in the category A product ID.  Please help!  
 
Thank you so much!
6 Replies 6

If Product ID # can be changed to a Single Line Text that will solve the issue with dropping the leading 0.  

Then if({Product ID #}=0...) can become if(LEFT({PRODUCT ID #}, 1)="0"...) 

Is there a way to extract just one number from a string of numbers without doing it manually?

I don't understand the question, or at least the specifics of it.  

What I suggested would return the first character in the string. 

If you're looking for the character at a specific position I would use a combination a of LEFT() and RIGHT() - RIGHT(LEFT({field name}, 4),1) should give you the character in the 4th position, but if 10 people respond they could probably give you 10 different variations. 

FIND() and SEARCH() can tell you if a specific character occurs in a string, and SEARCH() returns the location.

 

Sho
11 - Venus
11 - Venus

For example, this formula could be.
In this formula, the product ID needs to be String field.

SWITCH(LEFT({Product ID #},1),
  "0", "A",
  "1", "B",
  "2", "C",
  BLANK()
)

 

Helen_Harrell
5 - Automation Enthusiast
5 - Automation Enthusiast

What is the point of having the Blank() in here?

Sho
11 - Venus
11 - Venus

SWITCH(expression, [pattern, result ... , default])

The last argument is an optional default value that is not necessarily needed.
For example, you could put an error message here.