Combining conditional formulas with MID functions?

**larstrak** · 07-07-2011, 12:42 PM

To best describe the issue:

I have a pdf of a bunch of text, only a portion is useful to me. The info I want to extract is a UPC code. Sometimes, the UPC code is 11 digits, sometimes it is 12.

I understand how to use the MID function, but It doesn't always work. There are two reasons that this does not work:
1. Sometimes, the UPC code is 11 digits, while others it is 12 digits.
2. Sometimes, there are 3 characters before the desired UPC, and sometimes there are 4.

Is there a way to have Excel recognize when to have the MID formula start counting after 3 characters or four? Is there a way t have Excel know when my UPC is 11 digits or 12, and produce only the UPC?

Can I combine the necessary formulas in one cell, and have it display only my desired UPC?

**NBVC** · 07-07-2011, 01:12 PM

Is the UPC first set of numbers between the first and second space?

If so, try:

=TRIM(MID(A1,FIND(" ",A1)+1,FIND("^^",SUBSTITUTE(A1," ","^^",2))-FIND(" ",A1)))

**larstrak** · 07-07-2011, 01:44 PM

Thank you for your response. Your time and effort is appreciated.

Yes, the UPC is between the first and second spaces.

Your proposed solution does not work for me, unless I have plugged it in incorrectly. I have copied and pasted your formula and only the last 3 characters of my desired UPC are produced.

**NBVC** · 07-07-2011, 01:48 PM

See attached

**larstrak** · 07-07-2011, 01:57 PM

Thank you very much for your quick solution.

The reason it wasn't working for me is that I did not uncheck "Wrap Text".

I don't fully understand the formula that you have provided, but it works flawlessly for me. I will work on dissecting this piece by piece on my own to fully understand what you have provided.

Thank you so kindly for your help.