cancel
Showing results for 
Search instead for 
Did you mean: 

Change name based on last value in category name

SOLVED
hellyars
Asteroid

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?

Quasar

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