Cell Formating

Resolved · Low Priority · Version 2010

Tue 16 Sep 2014, 17:35 Delegate Jason said...

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.

Tue 16 Sep 2014, 17:44 Delegate Jason said...

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?

Mon 22 Sep 2014, 09:35 Trainer Jens said...

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

Attached files...

vlookup.xlsx

Mon 29 Sep 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Checking formulas with multiple operators

When dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right:

B = brackets
E = exponents
D = division
M = multiplication
A = addition
S = subtraction

It should be noted that multiplication and division are considered equal; as are addition and subtraction.

If you would like to check the order in which Excel is performing calculations in a formula, simply click on the cell containing the formula. Then go to Tools - Formula Auditing and select Evaluate Formula.

In the Evaluate Formula dialogue box that appears on your screen, click the Evaluate button to see how Excel calculates the formula result.

View all Excel hints and tips