Forum Discussion
Solved
Suggestions in Shortening a Formula
I have a csv file, that I get on a daily basis, that contains data for adding/updating products on my website. The 2 particular columns of data that I am dealing with in this inquiry is the Product Number and Manufacturer. The Manufacturer for some products is not consistent or nonexistent so I am trying to write a formula to make the Manufacturer consistent and complete across all products.
Example:
=IF(REPLACE(B2,4,99,"")="AAA","Alpha Co.","")&IF(REPLACE(B2,4,99,"")="BBB","Beta Co.","")
What this does is it checks the first 3 letters of the Product Number, which denotes the Manufacturer, then assigns the Manufacturer's name. The issue is that there are over 600 Manufacturers which makes the formula extremely long and far exceeds the character limit. Is there any other way aside from breaking down the formula into sizes within the limits?
=INDEX('proper names'!$B:$B,MATCH(LEFT('actual data'!B2,3),'proper names'!$A:$A,0))For the actual layout of your data you can try INDEX and MATCH.
=IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$20,MID(D2,4,3))),0)),REPLACE(D2,4,3,VLOOKUP(MID(D2,4,3),$A$2:$B$20,2,FALSE)),D2)Is this example similar to what you want to do? The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or 2021. The ranges of the formula can be adapted.
- Thanks for the reply.
There are manufacturers listed in a column but they are inconsistent (Alpha, ALPHA, Alpha Co., etc) and/or missing so I need to define the manufacturer solely on the first 3 letters of the product number.=IFERROR(IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$20,MID(D2,4,3))),0)),REPLACE(D2,4,3,VLOOKUP(MID(D2,4,3),$A$2:$B$20,2,FALSE)),D2),D2)Does this return the expected results? I assume that the first 3 letters of the product number are a unique definition of the manufacturer of course. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or 2021.
