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

1. ## 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

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

2. ## 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:

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

Hope this helps.

Pete

3. ## 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.

4. ## 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

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

Hi there,

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

6. ## 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. ## 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)

8. ## 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. ## 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. ## Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

Originally Posted by Glenn Kennedy
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. ## Re: Formula to Find a Match of String of Numbers in a Larger String of Numbers

Woo Hoo! You're welcome.

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