Need to add character before leading zero in a field

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

 

[T. Valko]

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.

 

[Gary''s Student]

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

 

[Bernard Liengme]

Sub addchar()
Set myrange = Range("D110")
j = 1
For Each mycell In myrange
Cells(j, 4) = "x0" & mycell
j = j + 1
Next
End Sub

Change "D110" 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

 

[MEAD5432]

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.

 

[Gord Dibben]

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

 

[Dolfan13net]

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 -

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.

 

[Dolfan13net]

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 -

Nevermind I figured it out thanks for the help