Formula to remove last letter to use it in data validation

Hello to all,

I'm trying to set a data validation in B1 from values in A1:A5.

A1: Car
A2: Papers
A3: Pencil
A4: Pants
A5: Shoes

What I want is show in dropdown list the words in singular (without the "s" at the end).

The formula below works if I introduce it in a normal cell,

Please Login or Register  to view this content.

or as array formula with CRTL+SHIFT+ENTER in this way

Please Login or Register  to view this content.

But I get the message that the formula has error when I introduce any of both in "Validation Criteria"-->Allow "List"-->Source.

May somebody help me fixing my formula or with other one that works in data validation.

Thanks in advance.

Hello Tony,

Thanks for answer.

Yes, I'm using the formula in source field, since I don't want to use an extra/auxiliary range if it is possible to avoid it.

Regards

When I use the first formula as data validation it works but shows only one word.

Is there a way to conver the resultant array of 2nd formula in something acceptable to use in data validation?

Thanks again for the help

Ok, thank you for the help Tony.

Regards