How to add a "0" (zero) to the front of existing data in cells

[Guest]

If anyone can help, I'd appreciate it..........so I don't have to add a "0"
to the front of a million cells......

 

[David Biddulph]

=0&A1
or Format Cells/ Number/ Custom/ "0"General

 

[Guest]

create a new column with the following formula:
=concatenate("0",C2) - or whatever the column/row number is that you want to
add the 0 to. It will create the new number - then just copy down for the
column
Might be too much trouble is you have alot of data

 

[Gord Dibben]

A million cells.............?

Sub Add_Text_Left()
Dim cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = ActiveSheet.UsedRange _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = "0"
For Each cell In thisrng
cell.Value = moretext & cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord Dibben MS Excel MVP

 

[Gatrie]

I need to add zeros to the front of existing data. When I use "custom" the
presentation of the data on the changes but the format of the data has not
actually changed. How do I get the "0s" to precede the data even when copied?

 

[Gatrie]

I need to add zeros to the front of existing data. When I use "custom" the
presentation of the data on the changes but the format of the data has not
actually changed. How do I get the "0s" to precede the data even when copied?

 

[Gord Dibben]

Custom formatting just changes the look but not the underlying value.

Run the macro found below.

It does not just custom format the data but actually adds a 0 to each cell
which becomes part of the cell data.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime.......... instructions for Excel 2003 and earlier but the
basics are the same for 2007.

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Custom formatting just changes the look but not the underlying value.

Run the macro found below.

It does not just custom format the data but actually adds a 0 to each cell
which becomes part of the cell data.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime.......... instructions for Excel 2003 and earlier but the
basics are the same for 2007.

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP

 

[Hotrie]

Can this macro be restricted to a specific column so that the "0" precedes 5
numbers in a specific column. I also visited the suggested websites but
could not modify the range to run the macro on a specific column.

 

[Hotrie]

Can this macro be restricted to a specific column so that the "0" precedes 5
numbers in a specific column. I also visited the suggested websites but
could not modify the range to run the macro on a specific column.