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

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

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

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

"Primbarbi" wrote:

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

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

On Tue, 15 May 2007 10:48:01 -0700, Primbarbi
wrote:

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

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" wrote:

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

On Tue, 15 May 2007 10:48:01 -0700, Primbarbi
wrote:

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

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP




On Tue, 12 May 2009 15:17:02 -0700, Gatrie
wrote:

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" wrote:

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

On Tue, 15 May 2007 10:48:01 -0700, Primbarbi
wrote:

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

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.

"Gord Dibben" wrote:

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 InsertModule. 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 ToolMacroMacros.

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


Gord Dibben MS Excel MVP




On Tue, 12 May 2009 15:17:02 -0700, Gatrie
wrote:

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" wrote:

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

On Tue, 15 May 2007 10:48:01 -0700, Primbarbi
wrote:

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