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