View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
MEAD5432 MEAD5432 is offline
external usenet poster
 
Posts: 28
Default Need to add character before leading zero in a field

Assuming the 0 is at the beginning of the information in the field so it
appears like 0XXXX..., you could try this alternative, non-VBA route:

I assume that the existing data you want to add to is now in column A.

-- Insert two columns in front of the field you want to edit. This gives
you two blank columns A, B and then your existing data would be in column C.

-- Type the new leading character, say "Z", you would like at the beginning
in the first cell in the column B.

-- In the empty column, at the beginning of your data (probably the second
row) type: =CONCATENATE($B$1,C2). This will add the prefix so your data
looks like: "Z0XXXX..."

-- Copy this all the way down the column to the end of the data.

-- Highlight all of column A and copy.

-- Highlight all of column B, right click and select "Paste Special..."

-- Select "Values" and click "OK"

-- Delete column A.

You can also delete the column with the existing data (Column C in the steps
above) if you desire. Since you pasted the values, there is no formula to
mess with your new data.



"Melin" wrote:

I need an automated way to add a character before a leading zero in a field.
I am importing information into excel from one software, manipulating the
information so I have only what I need and then exporting the information
into another software. We created a macro to keep the leading zero in Excel
but the software we are importing the information into does not allow leading
zeros. =( So we have to add a character to before the leading zero.

I am currently using Excel 2003. Any help would be appreciated.