+ Reply to Thread
Results 1 to 11 of 11

Formula to Find a Match of String of Numbers in a Larger String of Numbers

  1. #1
    Registered User
    Join Date
    08-16-2019
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Hi there,

    I have a string of numbers (tracking numbers) that when physically scanned, have an additional string of numbers in front of it. There is no set amount on the additional characters that precede the tracking number.

    In one column is the physical scan number, and in another is the actual


    Please see these examples here:

    Physical Scan: 420336119200112345678912345678
    Tracking Number: 9200112345678912345678

    The physical scan has an additional 8 characters, but it can be less or more than this, hence why I cannot use a MID formula.

    Is there any formula (wasn't sure if vlookup can somehow work) that will search the physical scan number and return the tracking number that partially matches. For example, I would run a lookup of some kind on 420336119200112345678912345678 and in the tracking number column, it would return 9200112345678912345678 because this segment matches.

    Please let me know if you require further details or clarification.

    Thanks!

    Matt
    Last edited by MattAquino; 08-16-2019 at 12:17 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,975

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    With the Tracking number in A2 and the Physical number in B2, you could use this formula:

    =IF(ISNUMBER(SEARCH(A2,B2)),"yes","no")

    or this, if you want to find out how many characters are in front of the Tracking number:

    =IFERROR(SEARCH(A2,B2)-1,"not found")

    It's not clear what you are trying to achieve.

    Hope this helps.

    Pete

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,988

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    With your short number in A2 and tyour long numbers in D1:D15,

    =ISNUMBER(SEARCH(A2,$D$2:$D$15))

    will return TRUE or FALSE. You can return whatever you want, but you didn't say. It might be good to see an Excel sheet, showing what you DO want to see and where you want to see it.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    1,240

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    I think you can use:

    Please Login or Register  to view this content.
    See example attached.

    WBD
    Attached Files Attached Files
    Office 365 on Windows 11, looking for rep!

  5. #5
    Registered User
    Join Date
    08-16-2019
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Hi there,

    Thanks for the hasty reply!

    I realized I actually had what I needed to find the other way around and have clarified more in the original post.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,975

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    It's still not clear what you want to achieve. You have a Tracking number and a Physical number column - are you saying that you want another Tracking number column for the matches?

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,988

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Is the tracking number always the same length?

    If so:

    =VLOOKUP(RIGHT(D2,26),A:A,1,FALSE)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-16-2019
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Ideally, yes I need another column that will verify if there is a match or not.

    Let me work on building a sample page; I can't use the real data as it has real tracking numbers in it.

  9. #9
    Registered User
    Join Date
    08-16-2019
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    I was thinking this would work, but the tracking number is not always the same number of digits.

  10. #10
    Registered User
    Join Date
    08-16-2019
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Quote Originally Posted by Glenn Kennedy View Post
    Is the tracking number always the same length?

    If so:

    =VLOOKUP(RIGHT(D2,26),A:A,1,FALSE)
    I used this with an iferror and added the other possible number of digits (22) and it seems to work for what I need.

    Thank you!

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,988

    Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

    Woo Hoo! You're welcome.

+ 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. Find short string in reference table within a larger string in lookup table
    By alertall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-16-2019, 10:10 AM
  2. formula to find first SET of numbers in a text string
    By krunk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2017, 09:06 AM
  3. [SOLVED] formula to find starting position and count numbers in a string
    By craig04 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-07-2017, 03:33 PM
  4. Formula to find a number in a string of numbers separated by a symbol
    By cslm001 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2012, 11:23 AM
  5. Replies: 2
    Last Post: 08-23-2012, 04:58 PM
  6. match number in a string of numbers
    By cmoore in forum Excel General
    Replies: 3
    Last Post: 05-02-2006, 02:50 PM
  7. Converting String numbers into real numbers:formula in VBA
    By Werner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2005, 04:41 PM

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