Formula to remove last letter of the Value in a field

[jord8on]

I have a field called “Category” where each of the categories are plural (i.e., “Widgets”, “Cogs”, etc.)

In the first field (record) i have a formula that grabs a product name, concatenated with the “Category” at the end.

I would like to have the “s” removed from the category name.

Here’s my current formula:

MFR & " " & {Model} & " " & {Category}

So essentially, I would like to remove the last letter, from the {Categroy} name.

Thanks in advance for any help on this!!

[Sam_Cederwall · Accepted solution]

I think you could do something like this to remove the last letter:

LEFT({Category},LEN({Category})-1)

This formula takes the left side of the category field then subtracts one off of the total length of the string, giving you ‘Category’ without the ‘s’.

You can plug this into your concatenate and it should give you what you need.

Hope this helps. Good luck!

[jord8on]

Sam_Cederwall:

LEFT({Category},LEN({Category})-1)

What a gem! That worked perfectly! Thank you so much for sharing this.

I may have a more complex question in the future. For example… one of my categories is “Accessories.” While most of the other categories you can just remove the “S” to make it singular, this would not work with the “Accessories” category. But I think I’ll be fine for a bit!

Thanks again for your help!

[Sam_Cederwall]

That certainly is more complex.

I’m not sure how you would go about this in a single formula, I’m sure there is a way, but I’m not quite advanced enough to figure it out. What I would offer as a solution however, is a Switch function.

You could create a column (we’ll just call it “Singular” for this) that contains the singular for each of the categories by doing something like this, for example:

SWITCH({Categories},"Boxes", "Box", "Widgets", "Widget", "Accessories", "Accessory")

This would populate a column with the singular based on what is in the Categories field. Then, for your concatenate, you could do this:

MFR&" "&{Model}&" "&{Singular}

This really only works well if your category field is a single select, if you have new categories coming in regularly, the Switch function would have to be updated regularly as each new category comes in.

Anyways, I hope this helps. Good luck!

[jord8on]

Brilliant @Sam_Cederwall! I will definitely use this when we start selling accessories! In the mention your initial formula is :ok_hand: