• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1959
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# I need a formula/s that will look up in 14 digit code for

This answer was rated:
I need a formula/s that will look up in 14 digit code for common 5 digit codes from a list.

e.g. 7570375130434, formula should find and return the 57037 (manufacturer code) and 51304 (product code) from 2 lists. Ideally it would then concatenate them.

Using LEFT, RIGHT or MID functions might not work as the error digits are not always in the same part of the 11-14 digit code, and (of course) the source number maybe anyhere from 11-14 digits long. The 2x 5 digit codes are always sequential.

e.g.

57037513041234
7570375130434

Both have the same 2x 5 digits in but are different lengths and the 2 x 5 digits are in different places in the 11-14 digit source number
Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

So to understand this correctly, you want to lookup multiple values in the same cell and then return another column such as price or something if both sequences are somewhere in the first collum?

Let me know exactly what you want to do.

Also if you could provide a sample sheet that would be useful

You can upload a sample to

www.wikisend.com

after uploading they provide a File ID#

paste the ID# XXXXX

if that site doesn't work you can try

www.ge.tt

this works the same way

they provide a link

paste that link here
Customer: replied 3 years ago.

Thanks Andrew,

File ID here 743902.

Your summary of the problem is almost right

"So to understand this correctly, you want to lookup multiple values in the same cell and then return another column such as price or something if both sequences are somewhere in the first collum?
"
Only I don't wnat to return price, I want to return a concatenation of the 2 values found in the first column.

I have 2 lists of products. One list is correct with 10 digit codes (1st 5 digits indicate manufacturer, 2nd 5 digits indicate unique products - commonly known as UPC codes found on bar codes)

The 2nd list contains incorrect codes that have anywhere from 11-14 digits. Somewhere in those codes are the same 10 sequential digits - maybe at the front, maybe at the back or the middle.

I need a formula to extract the 10 digits, in the 2nd list either as one 10 digit number or 2 x 5 digits, whichever is easier to write a formula for. The 2nd list has unique information not found in the 1st.

The destination of the data in the 2nd list is software that will only recognise a 10 digit number relating to a product.

That's all the detail I can give

here you go

the proper formula is in this sheet