Updating a formula that removes a specific text string from a concatenation in Excel

Andreamary
Andreamary used Ask the Experts™
on
The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:

=A2&IF(B2<>"","-"&B2,"")&IF(C2&D2<>"","-"&C2&D2,"")

Open in new window


I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Andrea
Product_Numbering.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
Try this

=A2&IF(B2<>"",IF(RIGHT(B2,4)="-AAU","-AAU","-"&B2),"")&IF(C2&D2<>"","-"&C2&D2,"")

Open in new window

Andreamary

Author

Commented:
Thanks very much, Ejgil, it works like a charm!

Cheers,
Andrea

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial