Help with searching for text and then displaying it.

Is there a certain formula or function that can help with searching a bunch of columns until the columns stop and then making a new column with that text that is found? I need to search for different versions of the text, too.

Here is an example.
"CAP CER 0.1UF 50V Y5V 0603" I need to find the text "0603" in this cell.
"DIODE ZENER 2.2V 250MW SOD323" I need to find the text "SOD323" in this cell. But sometimes it might be SOD-323. Also it needs to check for "0603" as well.
I tried making a whole bunch of nested IF statements but there are so many that I need to search for that the statement is getting too long. Is there a way to check it against a table I make in another spreadsheet with all the terms I need to search for? What would be the best method to approach this? I tried to record a Macro but that only records me pasting my clipboard and not pasting this code.
Here is my formula so far: =IF(ISNUMBER(SEARCH("0603",H2)),"0603",IF(ISNUMBER(SEARCH("0805",H2)),"0805",IF(ISNUMBER(SEARCH("1206",H2)),"1206",IF(ISNUMBER(SEARCH("2917",H2)),"2917",IF(ISNUMBER(SEARCH("0402",H2)),"0402",IF(ISNUMBER(SEARCH("0201",H2)),"0201",IF(ISNUMBER(SEARCH("1210",H2)),"1210",IF(ISNUMBER(SEARCH("SOT23",H2)),"SOT23",IF(ISNUMBER(SEARCH("SOD123",H2)),"SOD123",IF(ISNUMBER(SEARCH("CONN",H2)),"CONN",IF(ISNUMBER(SEARCH("SOT-23",H2)),"SOT-23",IF(ISNUMBER(SEARCH("tssop",H2)),"TSSOP",IF(ISNUMBER(SEARCH("8SOIC",H2)),"8SOIC",IF(ISNUMBER(SEARCH("14SOIC",H2)),"14SOIC",IF(ISNUMBER(SEARCH("2220",H2)),"2220",IF(ISNUMBER(SEARCH("HC-49/US",H2)),"HC-49/US",IF(ISNUMBER(SEARCH("QFN",H2)),"QFN",IF(ISNUMBER(SEARCH("2512",H2)),"2512",IF(ISNUMBER(SEARCH("8-SOIC",H2)),"8-SOIC","Not Found")))))))))))))))))))


Please help.
Thanks in advance.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The paperclip icon does not work. Please follow the directions in the last line in post #2.
Let us know if you have any questions.

Paste this Code in to a Macro Module

Please Login or Register  to view this content.

Change the formula in cell J2 to =FP(H2)

Change the formula in cell K2 to =IF(J2<>"",VLOOKUP($J$2,$M$2:$Q$28,4,TRUE),"")

I found the Macro in 3 places.

I deleted two occurrences and it now works.

This adds a user defined function to your workbook.

The function is explained below:=

Please Login or Register  to view this content.

the formula =FP(H2) runs the macro.

When you fill it down it will run the same macro for each cell eg =FP(H3)

I was just about to say that I figured it out . Thank you. I just had to google user defined function. Is there a way to make this into a macro that runs across each row of the table automatically instead of dragging down the fill option? As well as for the K2 formula?

I tried to work on my final formula with the FP function and the Vlookup but the formula is not working correctly(inside column L). Some of the results are the opposite of the argument. I can workout why that is later but can you please incorporate this formula into a macro? This is the endgame that I am going for. It needs to take the number under the assemblies and multiply the column that contains "Quantity" by itself and add the Vlookup result in column 5 and compare it to the minimum in column 4 to decide which is higher and paste that number, all with a click of a Macro.