Change name based on last value in category name

[hellyars]

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?

[Claje · Accepted 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

[tom_montpool]

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