Editing a list of cells

[excelnut1954]

I have a column of numbers, many that start with 053Z. They don't
all have the exact same number of digits. What I'm trying to do is to
go down the list, and change all those numbers starting with 053Z,
remove the zero, and put a D at the begining, thus it would look like
D53Z then the rest of the number.

Example:
If a number is 053Z123456, then the macro would make it D53123456.

I tried using the recorder, but when I use the macro down the column,
it makes every number exactly the same as the cell I used when I
recorded it.

I would appreciate any help.
Thanks,
J.O.

 

[Guest]

Sub changeData()
Range("A1").Activate
Do
If Left(Activecell,4) = "053Z" then
Left(Activecell,4) = "D53Z"
else
Activecell.Offset(1,0).Activate
Loop until Activecell = ""
End Sub

 

[Guest]

Another may to do it:
ActiveSheet.Range("A1:A1000").Select
Selection.Replace What:="053Z", Replacement:="D53Z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[John Coleman]

From your post, it isn't clear if you want the Z stripped as well as
the leading 0 changed to D. I'll assume its the later and that your
example was in error. If so, maybe the following can help:

Sub ZerosToDs()
Dim cl As Range
Dim s As String

For Each cl In Selection.Cells
s = cl.Value
If s Like "053Z*" Then
cl.Value = "D" & Mid(s, 2)
End If
Next cl
End Sub

Select the column (or part thereof) you want to change then invoke
this and it should work.

Hth

-John Coleman

 

[John Coleman]

An afterthought. The sub I wrote satisfies the specifications, but is
possibly not robust. In particular, it won't change strings which
begin 053z... (lower case z) and is also sensitive to any space
before the zero. These concerns can be addressed by replacing the line
s = cl.Value by s = Trim(UCase(cl.Value))

-John Coleman

 

[excelnut1954]

An afterthought. The sub I wrote satisfies the specifications, but is
possibly not robust. In particular, it won't change strings which
begin 053z... (lower case z) and is also sensitive to any space
before the zero. These concerns can be addressed by replacing the line
s = cl.Value by s = Trim(UCase(cl.Value))

-John Coleman









- Show quoted text -

Thanks, John
This helped alot.
I appreciate your time.
J.O.