Using IF with Find

Pete Spin

Hi,

I'm trying to simplify a cell's contents, to allow better cross referencing and help eliminate issues with typos. I create cells with a serial number, someone requests that serial number though a form.

Since the serial number can have a lot of variations requests through the form can have typos leading to the cross reference failing and extra work. examples of serial numbers P1234-56, X-1234-567, and 1234-567/890, I would like to remove the "-", and remove everything to the right of the "/" (including the slash). This formula works great for removing the symbols, call that column "Simplified SN"

=SUBSTITUTE(SUBSTITUTE([Vessel's SN]@row, "-", ""), "/", "")

This works great for removing everything from the "/" on:

=LEFT([Vessel's SN]@row, FIND("/", [Vessel's SN]@row, 1) - 1)

I tried doing it in two steps, first remove the "-" (and spaces) then apply the second formula (Column "Simplified SN 2) to remove the slash and everything after it, however if there is no "/" it errors, I know I can add the IF or IFERROR, and maybe gang the SUBSTITUTE and LEFT but the syntax and nesting has me lost. The serial number in the sheet I'm cross referencing must have all the symbols and numbers (for inventory reasons), but "Simplified SN" can be reduced as in the request I am cross referencing by name (so "Name" + "Simplified SN" = yes/no.

Thanks

Paul Newcome

Try this:

=SUBSTITUTE(IF(FIND("/", [Vessel's SN]@row) > 0, LEFT([Vessel's SN]@row, FIND("/", [Vessel's SN]@row, 1) - 1), [Vessel's SN]@row), "-", "")

Paul Newcome

Try this:

=SUBSTITUTE(IF(FIND("/", [Vessel's SN]@row) > 0, LEFT([Vessel's SN]@row, FIND("/", [Vessel's SN]@row, 1) - 1), [Vessel's SN]@row), "-", "")

Pete Spin

Thanks Paul, that's perfect!

So I understand; the "[Vessel's SN]@row) > 0" is the false condition? If "yes" it continues to the "LEFT" command, if "no" it skips LEFT and finishes the SUBSTITUTE? In other words "Substitute, but first find "/" if you can't find it, continue with substitute" ?

Is there a accepted methodology to nesting, in terms of what commands are first? Could you nest the LEFT command the same way, or is there a hierarchy.

Paul Newcome

That's how it is built.

For nesting, I try to keep formulas as short as possible. Technically you could have also written it like this:

=IF(FIND("/", [Vessel's SN]@row) > 0, SUBSTITUTE(LEFT([Vessel's SN]@row, FIND("/", [Vessel's SN]@row) - 1), "-", ""), SUBSTITUTE([Vessel's SN]@row, "-", ""))

But then the SUBTITUTE function is repeated. So I like to try to find a way to not have to repeat it. That's all that happened there.

Other times though it does matter, but there are so many possible variations with so many different functions, it would be almost impossible to list out absolutely everything and which way would be better or if a specific way has to be used.

Pete Spin

Thanks Paul, I appreciate your time. I have no coding experience and only limited excel formula building. I have inherited a lot of sheets and I have done pretty well with the simple stuff, and modifying/ fixing existing work.