Need to add character before leading zero in a field

  • Thread starter Thread starter Melin
  • Start date Start date
M

Melin

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.
 
Try something like this:

Will concatenate a "X" to all selected cells. Excludes cells with formulas
and empty cells. Change "X" to whatever character you want.

Sub AddX()
Dim cell As Range
For Each cell In Selection.SpecialCells(xlCellTypeConstants)
cell.Value = "X" & cell.Value
Next cell
End Sub


Select the cells where you want this to happen and rum the macro.
 
Try this small macro:

Sub xit()
For Each r In ActiveSheet.UsedRange
If IsEmpty(r) Then
Else
If Left(r.Text, 1) = "0" Then
r.Value = "x" & r.Text
End If
End If
Next
End Sub
 
Sub addchar()
Set myrange = Range("D1:D10")
j = 1
For Each mycell In myrange
Cells(j, 4) = "x0" & mycell
j = j + 1
Next
End Sub

Change "D1:D10" to the Range you needed
Change j = 1 to j = N where N is the row of the first cell of your range
Change Cells(j, 4) to Cells(j, M) where M is the column number of your
range )
(A=1, B=2. etc)
Change "x0" to whatever character you want followed by 0
best wishes
 
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.
 
MEAD

Nothing wrong with your posted method but just for info, it can be made much
more simple using one helper column.

Assuming 00xxx data in column A

In an adjacent blank column enter ="Z" & A1

Double-click to fill down.

Then do the copy/paste special bit.


Gord Dibben MS Excel MVP
 
MEAD

Nothing wrong with your posted method but just for info, it can be made much
more simple using one helper column.

Assuming 00xxx data in column A

In an adjacent blank column enter ="Z" & A1

Double-click to fill down.

Then do the copy/paste special bit.

Gord Dibben MS Excel MVP













- Show quoted text -
Click to expand...

I am also trying to do something similar but I can only get it to work
in one cell How do I copy it to all cells in that cloumn? You said
Double click to fill down but what am I supposed to be double
clicking? Sorry I am a rookie at this.
 
I am also trying to do something similar but I can only get it to work
in one cell How do I copy it to all cells in that cloumn? You said
Double click to fill down but what am I supposed to be double
clicking? Sorry I am a rookie at this.- Hide quoted text -

- Show quoted text -
Click to expand...

Nevermind I figured it out thanks for the help
 
Back
Top