+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

    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?

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

    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?
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by Tinbendr View Post
    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.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

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

    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.

  5. #5
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

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

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by JOHN H. DAVIS View Post
    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?

  7. #7
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by Tinbendr View Post
    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.

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

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

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

  9. #9
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by JOHN H. DAVIS View Post
    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.
    Attached Files Attached Files

  10. #10
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

    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.

  11. #11
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by Tinbendr View Post
    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?

  12. #12
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

    just copy and paste the link data instead of it showing up as a link
    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.

  13. #13
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    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?

  14. #14
    Registered User
    Join Date
    06-18-2014
    Location
    Atlanta, GA
    MS-Off Ver
    2007
    Posts
    8

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

    Quote Originally Posted by Tinbendr View Post
    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.

  15. #15
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,121

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

    Nothing jumps out at me, but if you want to email me the file, it's username at gmail.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Search Row 1 for value and return cooresponding column number.
    By gammaman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-22-2013, 07:19 AM
  2. Return characters after first number in a cell
    By sgdnyc in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-19-2013, 06:13 PM
  3. Replies: 8
    Last Post: 04-11-2012, 04:03 PM
  4. Search a list and return row number
    By freelance84 in forum Excel General
    Replies: 1
    Last Post: 02-04-2010, 12:43 PM
  5. counting characters in a cell:return the number
    By Glenn in forum Excel General
    Replies: 3
    Last Post: 08-23-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1