Question

Locked

In Excel how can I add data to an existing cell?

By Georgem5 ·
I have a row of cells that have a truncated zip code. For some reason this system truncates preceeding zeroes. I need a formula that will allow me to add a zero to the front of a 4 character cell. An example in J2 I have a 7075 which should be 07075. I tried looking at the Right-Mid-Left commands but I can only disply the contents of the cell I can't get the zero to insert. Any help would be greatly appreciated.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Format

by dogknees In reply to In Excel how can I add da ...

Try switching the number format of your cells to "Text". That will preserve leading zeros. The problem is that "numbers" don't have leading zeros by definition.

Collapse -

Reponse To Answer

by NotaBalloon In reply to Format

The Custom approach from LocoLobo is very neat but, if you put the cursor on the cell, only four digits are there - no leading zero. So if you're likely to use your 07075, in lookups or sumifs for example, then changing the format to text is preferable. You'll have to re-enter the data though as changing to text won't restore your zero. Alternatively you could use the Replace function to do this for you.

Collapse -

Format Cells

by LocoLobo In reply to In Excel how can I add da ...

Go to custom, type in 00000. That should work.

Collapse -

Try the concate function

If every cell in the column is supposed to have a leading zero then you can do something along the lines of going to an empty column and writing a simple concatenate function "=CONCATENATE("0",B5)" where B5 is whatever cell has the original data. Then copy this function down the column to update all the rows. Copy that column and in another empty column by doing a paste special and select values. Then when you confirm it is all correct you can copy this and do a paste special over the original column (again selecting values). Delete the two extra columns you just created and voila. Do make sure the column is set to text before pasting back or you will just loose the new Zero again :-) .

If you have to do it on selected values you can use a similar approach and do an if function with a nested find/replace. That will depend on being able to identify those cases which only need a leading 0 based on the other cells in that row.

Collapse -

Reponse To Answer

by Georgem5 In reply to Try the concate function

Thanks for the excellent. I actually used the join command to do the same thing. I am not sure the differnces between concatenate and join but both seem to solve the issue. Again thanks for the response.

Collapse -

Reponse To Answer

For all intents, join and concate are pretty much the same. There may be some unique little things that they each have but probably 95% or more of the time you can use either one and get the same result

Back to Software Forum
7 total posts (Page 1 of 1)  

Software Forums