coverting cell to number without it deleting zero's

**Natalie_M** · 02-22-2009, 08:36 AM

Hi Guys,

I have column of numbers used as item codes, they appear with a small green triangle in each cell.

When I select all cells a little blue square pops up with drop down options to convert to number- after doing this the green triangles disappear but then numbers I had listed as eg '043340' appear as 4334.

Is there way to make sure zero's are not deleted as some of my results from formulas end up being incorrect.

Thanks in advance for your help

Cheers,
Natalie

**ExcelNewby** · 02-22-2009, 09:22 AM

I just found out this yesterday and not sure how true it is. If the number appears on the left side of the cell then it is really a text. If the number appears on the right side of the cell then it is a number. You might need a formula =Text(B1,"000000")

Hope that help..I sure one of the awesome experts on here will give you a better answer.

**shg** · 02-22-2009, 09:34 AM

Natalie,

There are two ways to show numbers with leading zeros.

One is to use custom formatting (e.g., 000000 shows all numbers less than 100000 with leading zeros).

The other is to store them as text, which usually makes more sense (because part numbers are really strings, and you don't do math with them). Either enter them with a leading apostrophe, or format the cells as text in advance of entering.

The green triangle is a warning that you have a numeric value stored as text. Either ignore it, or suppress the display of the 'error'. (Tools > Options > Error checking, untick Number stored as text.)