Importing Excel Document adding apostrophe to certain cells.

James Gilfillan

I imported a simple 4 column excel document with around 8,000 rows. The columns are Item, description, UOM and cost. I am using the item and description to write a VLOOKUP on a separate sheet.

For some reason in the item column (Which are all item codes) if an individual cell has only numbers, it adds an apostrophe BEFORE the set of numbers. For example, item code 100200 will read '100200 when I click into the cell. If there is even one letter in the cell, this will not occur. This is causing my VLOOKUP to return a NOMATCH.

There are no apostrophes on the excel document and I cannot find a reason for why this is happening.

Is there a simple fix? Please help!!

James Gilfillan

SOLUTION

I had to change the excel column to text to column which removed my leading zeros on import. I then wrote a simple formula to add the leading zeros depending on the amount of numbers in the related cell.

Matt Lynn-PCG

@James Gilfillan Unfortunately this is a common import issue even when using something like data shuttle. Also not uncommon back in the excel only days. It has to do with the format of the original data and while there are some "fixes" I've had repeat issues where it starts happening again.

The best way I've dealt with it is to just do a helper column and use either a =value() formula to convert it to the value it shows… and if that don't work I've done a =substitute() formula to remove the ' from the string. Once that is gone you can put that in a value to get the numerical value back. Something like

=value(substitute([cellref]," ' ", ""))

James Gilfillan

Thank you for the prompt response. There is an issue with this solution as I have up to 5 leading zeros that this removes.

Is there a way to work around this?

James Gilfillan

SOLUTION

I had to change the excel column to text to column which removed my leading zeros on import. I then wrote a simple formula to add the leading zeros depending on the amount of numbers in the related cell.