need help with excel

  • KweiJune
  • Born
  • Born
  • KweiJune
  • Posts: 4

Post 3+ Months Ago

if i wanted to keep a zero infront of a number, how do i get excell to read the number with a zero infront of it?
ex: 03912
but when put in exell, it reads it as : 3912
so how do i get it to read it as :03912
????
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Format Cell(s) :)
  • KweiJune
  • Born
  • Born
  • KweiJune
  • Posts: 4

Post 3+ Months Ago

i'm kind of new at excell, so could you give me better directions?...
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23454
  • Loc: Woodbridge VA

Post 3+ Months Ago

Right click on any given cell and select Format Cells. In the number tab, select "text". I know that seems a little odd. The default is General and even if you choose Number, the leading 0 will disappear after you exit the cell. The only way to keep the leading 0 is to format it as text.

Keep in mind, if you format a cell as text, you probably won't be able to do any calculations with the numbers. So it depends on what your purpose is as to how to format it.

Also it's nice to know that you can highlight an entire column, or row, or blocks and right click on the highlighted area. Then format the cells the way you want it and it will change all of them to the new format.
  • KweiJune
  • Born
  • Born
  • KweiJune
  • Posts: 4

Post 3+ Months Ago

it took away all of the zeros, but when i add in a new zero it stays.
now i have like 3000 different cells to add a zero infront of....
i guess the person that sent me the file messed up.
so now i need help with this problem.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23454
  • Loc: Woodbridge VA

Post 3+ Months Ago

KweiJune -- it's a little difficult to understand what you were saying in the last reply. Are you saying that you have a file that did have the leading zeros? If so, then those should have already been formatted OK. What exactly are you trying to do, add new cells? or revise existing ones.
  • KweiJune
  • Born
  • Born
  • KweiJune
  • Posts: 4

Post 3+ Months Ago

What i have is about 3000 adresses and most of these adresses have zip codes starting with zero.
ex: 03824
so i did what you said and changed it to text format. Then it changed all those numbers by taking the zeros infront if the numbers away. Now when i add a zero back in front, excel reads the zero, but now i have to go through like 3000 different numbers and add in the zeros.
So, how can i make it leave the zeros?
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23454
  • Loc: Woodbridge VA

Post 3+ Months Ago

Well hopefully you didn't save the changes to the original file (or you made a backup of the original file before you made changes). If so, close out without saving and reopen the file and it should be back to the way it was with all the zeros there. I still don't really understand what you're doing though. Obviously you selected all the cells and changed to text like I suggested, but it sounds like they were already formatted as text. There's no other way I know of to have the leading 0 there. So if all you're trying to do is add new cells just format the cells that you add to text and you should get what you want.
  • icyroadz
  • Student
  • Student
  • User avatar
  • Posts: 66
  • Loc: Edmonton, Alberta, Canada

Post 3+ Months Ago

I think I understand, maybe. Say you've got a column of zip codes, your friend entered them as numbers but didn't notice that "03875" comes out as "3875". Insert a new column beside your old zip codes, then copy and paste this function:

=CONCATENATE(IF(A1>9999,"",IF(A1>999,"0",IF(A1>99,"00",IF(A1>9,"000","0000")))),TEXT(A1,0))

If the column with the old zips is "R" and your list starts on row 8 paste it into R8 change the "A1"s to "R8"s in the formula then copy/paste that cell all over your column. You should end up with a text output of this formula that has the leading zeroes attached.

Finally to clean up insert another new column copy that column and go edit>paste special>values, then you should have just text with leading zeroes, and you can delete the first two columns (the old zips and the formulas)

Good luck
  • ladeyna
  • Born
  • Born
  • ladeyna
  • Posts: 1

Post 3+ Months Ago

I am working on a spread sheet from excel. I have the employee name then the start day then January Accrual -then January Used-and as follow for the rest of the year i want it to give me a total of the Accrual and a total of the used - and a subtotal as well as a total available. can someone help me i can email the spread sheet i am so new to excel.

please someone help.
  • fujilives
  • Born
  • Born
  • fujilives
  • Posts: 1

Post 3+ Months Ago

Just stumbled into this with a google search result - I had the same issue keeping leading zeros / stopping zero's from trunicating when cells were called from a formula (despite the formatting of a cell defining that it should show leading zeros, and the originating cell data containing said leading zeros).

Anyhow, for a quick fix, in my case I wanted to add .JPG to a bunch of model numbers for easily naming shopping cart images, and I didn't want to have to update that field every time I added a product or decided to change image formats (say, from jpg to png) so I made a formula to do that.

In my case, the SKU data I wanted to pull started from R4.

=TEXT(R4,"0000")&".jpg"

This made the number show as:

0345.jpg

The second block of zeros in the formula can be modified/extended in mostly the same way you can in cell formatting options - so, for example, you could do

=TEXT(R4,"00000 0-0")&".png"

and that same 0345 would generate output something like

00003 4-5.png


Hope this helps someone!

Post Information

  • Total Posts in this topic: 11 posts
  • Users browsing this forum: No registered users and 68 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.