If a Last Name ends with a S I want to change the Category from whatever it is to something else like cookies.
I assume I need to use a Right function, but I don't know how to mix this with an if statement.
Any thoughts?
Solved! Go to Solution.
Hi,
Here's a quick example of exactly what you're looking to do:
IF RIGHT([Last Name],1) = "S" THEN "Cookies" ELSE [Category] ENDIF
Notably, you can also use ENDSWITH() To accomplish the same thing and avoid calling out string length
IF ENDSWITH([Last Name],"S") THEN "Cookies" ELSE [Category] ENDIF
I wanted to point out that if the case of the letter "S" is important, the solutions from @Claje handle case differently.
In the first example, the RIGHT() syntax is case-sensitive so the replacement will only work with an uppercase "S". To make the RIGHT() syntax example case-insensitive, you could:
1)
IF
RIGHT([Last Name]),1) = "S" OR RIGHT([Last Name),1) = "s"
THEN
"Cookies"
ELSE
[Category]
ENDIF
2)
IF
RIGHT(UPPERCASE([Last Name]),1) = "S"
THEN
"Cookies"
ELSE
[Category]
ENDIF
In the second example, the ENDSWITH() syntax defaults to being case-insensitive, therefore, as written both 'S' and 's' would become "Cookies". If case is important, you could add the third parameter to the ENDSWITH() function:
IF
ENDSWITH([Last Name],"S",0) THEN "Cookies" ELSE [Category] ENDIF