Questions

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

+
0 Votes
Locked

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

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.
  • +
    0 Votes
    dogknees

    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.

    +
    0 Votes
    NotaBalloon

    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.

    +
    1 Votes
    LocoLobo

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

    +
    0 Votes

    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.

    +
    0 Votes
    Georgem5

    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.

    +
    0 Votes

    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

  • +
    0 Votes
    dogknees

    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.

    +
    0 Votes
    NotaBalloon

    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.

    +
    1 Votes
    LocoLobo

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

    +
    0 Votes

    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.

    +
    0 Votes
    Georgem5

    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.

    +
    0 Votes

    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