I need a formula that will try and find part of text in a designated cell and then return a reult base on what it finds.
I have it working at the moment with 6 searches but need to add more?
Here is the formula i have atm
=IF(ISNUMBER(SEARCH("DG",F146)),"DGREY",IF(ISNUMBER(SEARCH("OP",F146)),"OPAL",IF(ISNUMBER(SEARCH("TO",F146)),"TOFFEE",IF(ISNUMBER(SEARCH("LG",F146)),"LGREY",IF(ISNUMBER(SEARCH("BRO",F146)),"BRONZE",IF(ISNUMBER(SEARCH("CL",F146)),"CLEAR"))))))
But once i go to add another search it won't allow it as excel has a limmit on searches in one formula.
This will also be adopted in other instances for eg.
it looks in the cell that as "SHPC-75G1.8OPAL" from that as above i want it to find part of the text and return a reult in a diferent cell. so for this eg with the above code it will see "OP" and return "OPAL" in the other cell. if i can get this too work i can then adapt it to look at this code and pull all the information out of it. "SHPC-75G1.8OPAL" - 75 meand it is .75 so it will return .75 in another cell, G is the profile and it will reurn Greca in a different cell, then the numbers 1.8 will return 1800 in another cell. that is how i will adapt this code but need a way to not use VLOOK up because there are too many codes to start a data base to do this.
So if you are able to help me it would be greatly appreciated
Bookmarks