Jason has attended:
Excel Advanced course
Cell Formating
I am trying to do a vlookup against barcodes which sometimes have leading zeros, when I format the numbers as text (to keep the leading zero) the fields do not update until I go into the formula bar and press enter, fine to do this manually on 1 or 2 cells, but not on a large data set. Any help appreciated, thanks.
RE: Cell Formating
To clarify the problem... I have two tables of data and the number format is not always the same, so this fails the vlookup. When selecting the columns and changing the format so that they match in both tables, it doesn't always update, until I go into each cell and click enter in the formula bar. Hopefully this is a bit clearer?
RE: Cell Formating
Hi Jason,
Thank you for the forum question.
And sorry about the delay answering the question.
Formatting can be a pain in Excel and many times I had to get around problems like that by using functions.
I have attached a workbook where I on sheet1 lookup data from sheet2. I assume that the bar codes you need to lookup have the same number of digits. You can see in sheet1 column C that I use the Len and If function to test the number of digits and if it is less than 8 I want Excel to add 0 in front of the bar code number.
I have needed to do this before with leading zeros because of the problems with formatting in Excel.
I hope that this can help you and get around the problem with formatting and leading zeros.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
vlookup.xlsx
