Copy Text Inside of Text String

Okay, I have one column full of Item #s and Descriptions. I have another column that I want JUST the Item# in it.

For example:

Column 1:
Gyroscope REF#1234 Bike Thing

Column 2 Should Be:
1234

Column 1:
BallWall Bikes Ret# 12456 Helmet Thing

Column 2S hould Be:
12456

I don't know how to do this (I do know how to do VBA mildly, and I am pretty good with formulas). I also have a database of every Item# that could be in that cell.

Formula:

Please Login or Register  to view this content.

I can use this, except the problem is that I need it to be able to search a range or database of potential words and then make column 2 equal to any word it finds instead of a spcified word.

To answer your question, the item # might be a word or number, or combination of both. It might not be the only word or #. A # is usually before the item#...Example: ITEM# 1221 or ITEM#1221, etc.

That formula works for some of it...but it isn't always in that format. I need a formula that will search a list of words for a match in the desired cell.

If there is a match, the result will be that word.

It works well enough.

Hi Guys, new to the forum but a long time user of the formulas published here. Having a trouble at this formula. So far the formula work to a certain level...

I'm working with a data as below.

22.04.2014 08:01:30 John Doe
Report number:OC-TPI/KLM-SA/AJU-123
Title: Report For Pancake Taste
Tasting date: 08/09/2013

Result: Delicious Pancake!.

My end goal is to extract "OC-TPI/KLM-SA/AJU-123"

I have successfully do that using this code.

Please Login or Register  to view this content.

But my database not all the "Report number:" is using the word number, some will look like this "Report No.:" So I modified the code to look for "OC-" which is the beginning of the report no name. as below:

Please Login or Register  to view this content.

But the problem now, it output "TPI/KLM-SA/AJU-123" missing the "OC-".

Try to tweak the code but I'm still stuck.

Note: the report number always start with "OC-" and always ended with char(10)[Alt+Enter]

Thanks