Search/return a cell with a large number of characters from a list (column)

Hi,

I am currently working with a database that has a large amount of characters in some cells that contain values I want to return.

The simple left, right, and middle functions won't cut it here.

Here is an example of what I need:

Currently trying to write a VBA function in excel that searches from a list of numbers (ex: 3849321 - 3992010) in column C:C of one sheet from a cell that has a crazy amount of characters in a cell (124 217491249 vawe fwaefh 3849321 012312312 3) column D:D in another sheet and returns the exact number from the list.

Can anyone offer some help?

Originally Posted by Tinbendr

If you already have the number (Col C), why do you "returns the exact number from the list. "?

What is it you want to learn from finding the number?

The Col C number is a number that is from our internal system. Column D is external data that I am needing to match with our number and extract. They sent the data in the wrong format, so this "billing number" I need has to be extracted from a string of text within one cell.

See if this helps. It assumes Sheet1 has the regular numbers, and Sheet2 has the funny numbers. It places the results in Column E of Sheet2.

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

See if this helps. It assumes Sheet1 has the regular numbers, and Sheet2 has the funny numbers. It places the results in Column E of Sheet2.

Please Login or Register  to view this content.

Thank you! This works, but only if the number I am trying to locate is in the exact same row position as the number I am trying to locate. Can it locate it from a list no matter what row the number is from?

Originally Posted by Tinbendr

You can use this formula to determine if billing number is in list.

Please Login or Register  to view this content.

I appreciate the help, I used the search function, but can it return something other than true or false? Could it return the actual billing number? Keep in mind, the billing numbers are in different locations within the list and not 1:1 with the lines from which I am wanting to extract them.

Can you provide a sample, showing your layout before the macro is run. And then how it would look after the macro is run.

Originally Posted by JOHN H. DAVIS

Can you provide a sample, showing your layout before the macro is run. And then how it would look after the macro is run.

Here is an attached example of what I want... column C (list of numbers we have internally), and the outside number (column d) is what I am searching in to return the number from the list (column e)

The actual sheet has hundreds of thousands of entries.

Originally Posted by Tinbendr

How about his? It creates a hyperlink on Sheet2 that links back to the row number of sheet1.

Please Login or Register  to view this content.

This does work in located the correct entry and linking to it, but is there a way to just copy and paste the link data instead of it showing up as a link?

Thank you so much, reps to you! This worked perfectly... I am rather new to VBA so your help is much appreciated.

If I want to link to external workbooks, is there any special formatting I should use?

Originally Posted by Tinbendr

I don't understand this.

Be adventurous. Try some things!

Replace the hyperlink with
To put the number next to the source.

Please Login or Register  to view this content.

Will put the number next to itself.

Please Login or Register  to view this content.

Although, when I run it on the large set of data I am getting a run-time error "13" Type mismatch. Any solutions to this error? I tried formatting all the cells to numbers but that did not work.