let

HOW TO KEEP LEADING ZEROS ON LEFT IN EXCEL 2010? VIDEO TUTORIAL



Sharing buttons:

we've seen in an earlier lesson that

Excel will quite happily take text

values or numeric values and that if we

enter some text here so if I type my

name come down and type my age down 30

left aligned text values and right

aligned numeric values now there are

times when you'd like to tell Excel to

treat the number as text and those times

might be when you would like a leading 0

for example if in I move down a couple

of cells on our to type 0 0 0 35 perhaps

that would be a product number or an

identification mark of some kind when I

press return Excel takes away the three

leading zeros thinking well we don't

need any leading zeros on a number I

would like it to leave them there there

are a couple of ways we can do that

first of those is to put in an indicator

which is a single apostrophe if you type

a single apostrophe before you type any

numeric values so if I put my leading

zeros in and 3 5 what you will find is

that single apostrophe tells Excel to

treat the following characters as text

characters not numeric when I press

ENTER you'll see that a all the contents

left aligns them because they're now

being treated as text and B I do not

lose my leading zeros because we're told

it is treated as text I also get this

little green indicator mark lamp if I

click and hover and then click the

exclamation mark it's trying to tell me

that this number is stored as text would

you like to convert it to a number well

actually I wouldn't because I've done it

on purpose

so I'd like it to ignore that error and

then the green little triangle

disappears you'll also notice that the

apostrophe is not displayed in the cell

so I cannot see that here however up in

the formula bar I can see the apostrophe

so I know I've put it there but

obviously it won't print or it's not

displayed on the screen there is a

second way of getting Excel to treat and

you Merrit value as a text value and

that is to preform at the cell so if I

pick on this cell here

and go to the home ribbon so I need to

preform at least sell as a text sell

comp here in the number formatting

option and in the drop-down choose text

text now that cell is being formatted as

a text cell so I can go into it and

perhaps do my same thing by leaving

zeros on my 35 and return and it's now

treated as text you will see you get the

same little warning symbol all this

number stored as text yep don't wanna

convert it to invert no I would like to

ignore the error and the difference

between this cell and this cell is that

this one does not have the apostrophe in

it whereas this one does but I had to

preform at this cell before typing the

numbering I can't do it afterwards

because it's already taken the leading

zeros off of that stage so you have to

preform at any cell you would like to

enter numeric values as text values and

then you can use them as text values in

that cell or just type the apostrophe as

you type the cell which personally I

find a lot easier this is quite useful

for identification numbers that would

have leading zeros in there or telephone

numbers that tend to have zeros at the

front of them you don't want those zeros

to be lost or you tend to find things

like zip codes could be altered into

different number styles or Bank sort

codes can be altered into different

number styles so any way of trying to

use numeric values really as characters

rather than for mathematical formulas

it's worth formatting that cell has

texts either manually by picking a cell

going to format and choosing text or by

just typing the single apostrophe before

you type any numbers in and then you'll

find Excel will treat those numbers as

text values left aligns them and you

cannot do any formulas on them