Formula to Remove, or not remove, certain text from a cell.

[Jake_San]

I'm working on a worksheet that helps to manage some tooling inventory at my work and ran into a problem, so I was hoping that you guys could help me out.

I have a formula that automatically brings my attention to any tooling we have low inventory on, and I have another field that automatically populates and shows me the average usage of the piece of tooling from the last 3 months. However, if we have a design change pending on that piece of tooling we add a '-1' to the end of the shelf number.

When there is a shelf number with a '-1' attached to it that is low on inventory, I can't see the average usage of the piece.

So I have created a formula so that if I had a piece of tooling called 5B002-1, it automatically removes the -1 for me and the original shelf number will be displayed. (5B002)
(Formula Used: =LEFT(J8, SEARCH("-", J8)-1) )

However, if there is no -1 on the shelf number, this formula will result in a #VALUE result.

Is there anyway I could add in a IF X DO Y sort of conditional into this formula?

Basically 5B002 would result in 5B002, but 5B002-1 would get turned into 5B002.

Sorry for the round-about explanation of the problem, but I would appreciate any help.

Thank you,

EDIT: I've added a sample workbook with the problem area highlighted in purple.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[Jake_San]

I've added a sample workbook to the post.

Thank you for the help,

[AliGW]

Try this copied down:

=IFERROR(LEFT(I18, SEARCH("-", I18)-1),I18)

[Jake_San]

Thank you for the help Ali, that solved my problem!

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.