My nested formula is returning a #VALUE! error, and I'm not sure why.

=IF(AND(RIGHT(J2)="s",SEARCH("-",J2)),LEFT(J2,(LEN(J2)-1)),J2)

My formula is supposed to check a cell to see if it contains a "-", then also check if it has a "s" at the end. If it meets both conditions, it should remove the "s". This part works.

The issue is if the cell does not contain a "-" at all. If the formula is used on a cell without a "-", it returns a #VALUE! error. Ideally, the formula should completely ignore the cell and just spit out the cell in its entirety, unedited. I can't quite figure out how to solve this after a lot of Googling.

Any help is appreciated.

This is because the Search function returns #VALUE! Error if the string doesn't contain the searched value "-"

You have to test if the result of search is valid or error.

Try
=IF(AND(RIGHT(J2)="s",ISNUMBER(SEARCH("-",J2))),LEFT(J2,(LEN(J2)-1)),J2)

=IF(AND(RIGHT(J2)="s",ISNUMBER(SEARCH("-",J2))),LEFT(J2,(LEN(J2)-1)),J2)

seems to work. Thank you everyone!

You're welcome.