Excel Format Numbers: How to turn "1" into "001" or "EN001" - No Formula needed

Sharing buttons:

hello and in this video we are going to learn how to turn one into 001 in Excel

all the private employers had to add leading zeroz and then later on also

going to learn how to add text and leading zeros before number and still have Excel treat

it as a number such as EN001 ok so let's get to it in many instances we

would have to work with ID numbers that men Natura need to have a few zeros

before the actual number in this video we are using student name is to an idea

that an example but in reality it could be anything from prodcut numbers such

numbers customer ID membership I D some problem is we want to input

ID numbers as a three-digit numbers such as 001 but whenever we

press Enter

Excel will automatically removes the two zeroes before the number one which is not what

we want of course a workaround a solution

first been put a single quote before entering the number and this will force Excel

accept it as text or we can format the number as text and basically anything

that is typed there will

appear exactly as it is entered but both these methods have problems now that this new

numbers are still a text that will be imported bow to filter or sort them by largest to smallest

to do a shoot out from largest to smallest are to perform any basic

calculation if I want to put every twenty students into a separate group

and a scientist I cannot do that in reality it will be really really messy

if you have watched it out let's say a thousand customers and you want to

select only the first 50 customers fall special promotion so what can we do

lets guests must select the whole column and then open the numbers dialogue some

of you may already have gone thank you did that there's nothing suitable but

really just keep on looking a little bit harder until you go to the last category

which is custom and in the type in my little bit confusing but don't greatest

precio 0 still there and basically what this will do it that it will show that

we went a three digit number that we want to keep the leading zeros whenever

possible so let's press OK and see what will happen and you can see that oil

sales now have leading zeros and we'll just go to test five percent in 2007 yes

actually we can also test and 29 and it will be automatically converted in 2009

so let's try with 21 1230 now added that it will work but actually if you look

close second in both sales of format is a little bit funny and if you look

closely you will see them

here and this is because his numbers are being stored as text so now we will need to

convert these two numbers first and then a platform at Pinterest and now ourselves

with leading zeros in sexy the way we want it to me if your idea number of text

and leading zeros Frank Lampard least engineering students and we want that I

D numbers to be en001 you might think we'll need to use especially Excel

functions such as concatenate

Join strings but they will be quite messy because his function only what

would text and not text and number of simple solution is to go back to format

number though to cash them

custom and in the typed out on which ever text we want

enter which is an al Qaeda

en then you remember that if we rent attacked them inside to double quote ok

and yet now we had made to still see cease being treated as numbers and we

can sort and filter and the way we like

largest smaller and that's it

we have the best of both worlds without any messy formula