vendredi 21 juin 2019

How can I remove unnecessary information (clean up) from a set data list

I have a set group of model names that are sometimes added into my master list with unnecessary information, that I would like removed. For example, "GMIG-Toto advanced Systems"; There is a unique identifier, in this case "GMIG-" which I would like removed. However, there are some instances I need to remain. With the set model names listed below I need specific info removed based off of the following logic.

Model Names: Gogosystems 101, GMIG-Toto advanced Systems, 1546-Gogo advanced-Drivers, ZZZ-Advance Protocol

Logic Routes: Q1.Is there a "-" in the first 5 letters/numbers to the left of the model name? If yes, proceed to route A1. If no, go to route B1. A1. Does the first 5 letters/numbers contain "ZZZ"? If yes, proceed to route B1. If no, go to route C1 B1.Leave the model as is. C1.Delete the hyphen if in the first 5 letters & all information to the left of the hyphen & place the remaining value in that cell (all info right of the hyphen).

Note: The hyphen should only be in the first 5 letter to the left of the model name. Some model names do contain hyphens after the first five letters but we need those ignored.

I have been successful of removing the "ZZZ" & "-" from the models, using the formulas listed below. However, if there is a hyphen in the back of the model name, it has been excluding information when it shouldn't. For example:"1546 Gogo advanced-Drivers", would return as "Drivers". Secondly, the hyphen check has been superseding the "ZZZ" which incorrect to my logic listed above. Finally, I have been unsuccessful in adding an "if formula" to the end to return the model name if there is no "-" or "ZZZ"

Keep in mind Cell A1 contains the model name. =IF(LEFT(A1,5)="ZZZ",A1,REPLACE(A1,LEFT(5),FIND("-",A1),""))

Results should be consistent with my logic listed above.

Aucun commentaire:

Enregistrer un commentaire