Nesting IF statements with the FIND function

Manny Tavares

Hello all,

I am having an issue nesting an IF statement I created for a specific type of sheet.

Lets say we have a column named Full Name and it has values in it like "This Long Name - A new value"

The original function would be something like:

=IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 16,20), [Full Name]@row)

This statement will check if the Full Name column has an entry with "This Long Name", replace it with "TLN" and add the rest of the row details to the end, resulting in: TLN A new Value. Otherwise if "This Long Name" isn't found, it just uses the original Full Name found.

This works fine, but if i try to nest this function it always gives me a #invalid argument

=IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 17,20), [Full Name]@row,IF(find("This Old Name",[Full Name]@row)=1, "TON"+MID([Full Name]@row, 16,20), [Full Name]@row)

Any help would be appreciated!

Manny Tavares

I have figured out my issue; you apparently only need to add the False argument once.

The correct statement is

=IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 17,20),

IF(find("This Old Name",[Full Name]@row)=1, "TON"+MID([Full Name]@row, 16,20), [Full Name]@row)

Manny Tavares

I have figured out my issue; you apparently only need to add the False argument once.

The correct statement is

=IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row, 17,20),

IF(find("This Old Name",[Full Name]@row)=1, "TON"+MID([Full Name]@row, 16,20), [Full Name]@row)