Adding numbers or characters to existing numbers

[Guest]

I have a column of numbers 005445688996
005675556543 and so on.

About 500 in all. I want to add a zero to the beginning of all the numbers.
So example the first one reads: 0005445688996 and so on down the entire
column.
Please help.
Jannie

 

[Don Guillett]

try this

for each c in range("a2:a"&cells(rows.count,"a").end(xlup).row))
c.value="0" & c
next c

 

[Guest]

My column data starts in cell C3. I tried this formula but I don't think I'm
saying it right. Could you please write it out exactly like I should say it
so I understand exactly what you mean. Thanks.

 

[Don Guillett]

This is NOT a formula. It is a macro that must be copied into a module and
executed.

Just tested. Notice the ' before the 0

Sub addzerotostartofcell()
For Each c In Range("c3:c" & _
Cells(Rows.Count, "c").End(xlUp).Row)
c.Value = "'0" & c
Next c

 

[MartinW]

Hi Jannie

If they are all going to be 13 digits then you could format the
cells as custom 0000000000000

HTH
Martin

 

[Guest]

Hi Martin:
when I try that it doesn't work because the numbers came from another source
and it doesn't recognize the numbers as values. I tried copy and paste
special to make them values but it's not working. I had to format the cells
as numbers but it drops the leading zero so I'm trying to add it back.

As far as the "macro module "thing I have never done that so I don't even
know where to start.

Isn't there an easier way for me to do this?

Jannie

 

[David Biddulph]

If your existing "numbers" are text strings, then =0&A1
If your existing numbers are actually numbers, but formatted, then you may
need something like
=0&TEXT(A1,"000000000000")

 

[Don Guillett]

In the helper column try this formula. Then copy/paste as values
="0"&c3

 

[MartinW]

Hi Jannie

< I tried copy and paste special to make them values but it's not working. I
had to format the cells>

By this do you mean you copied a 'blank' cell, then selected your data, then
paste
special, check 'add' and OK.

This should convert all your data to numbers and then applying the custom
format should put all your leading zeroes in place.

HTH
Martin

 

[Guest]

YES! David That worked perfect. Thank you all for helping!
Jannie