Using VLOOKUP referencing other cells with formulas.

[TextMonkey]

Hi all,

A little background:

I have a sheet where I import data from a raw text file. That data has item codes ranging from "00000" to "99999".

To prevent excel removing the leading zeros, I converted the values to text using the formula:

Please Login or Register  to view this content.

. To make things simpler, I have used this formula for all values in the range irrespective of whether the have leading zeros or not.

The reason I did that was because I have used the formula

Please Login or Register  to view this content.

to pick out certain groups of numbers and I couldn't think of a way to achieve this if I had a mixture of strings and numbers in the same range.

The issue I have is that when I attempt a VLOOKUP on the range above

Please Login or Register  to view this content.

It is fine where there is a match, but where there is no match, instead of returning the blank I have requested (or think I have), it is returning "0's"

Is there a way to prevent this, or a better way to handle the data?

Many thanks for your time as always!

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[TextMonkey]

Hi again,

Well as it happens, when creating the example you requested I noticed the issue didn't arise. From there it didn't take long to figure out the raw ASCI import needed cleaned.

Sorry to waste your time!