Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy, Computer Manager

Category: Microsoft Office

Satisfied Customers: 1896

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

"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.