Macro to convert specific Numbers to Letters...

[Steven Ratliff]

Hello,

I was wondering if anyone would have an Excel macro that would allow
me to convert any number starting or ending with a ZERO to a letter
such as X,Y,or Z. If not, just a macro that would convert any ZERO to
an X would work fine. I'm tired of having to change my columns to text
every time I move something around or do a TEXT to COLUMN conversion.
I work with many pseudo numbers like: 0012, 0003450, etc. Of course,
since these are not real numbers as such, EXCEL can't accept them.
Thanks for any help or advice you can offer.

SGR

 

[Don Guillett]

Is this what you want?
Sub convert0()
If Left([m5], 1) = 0 _
Or Right([m5], 1) = 0 _
Then [m5] = "x"
End Sub

 

[immanuel]

Try using Replace()

Something like cell.value = replace(cell.value,"0","x")

/i.

 

[Tom Ogilvy]

for each cell in selection.SpecialCells(xlConstants,xlTextValues)
cell.Value = Application.Substitute(cell.Text,"0","X")
Next

to reverse

for each cell in selection.SpecialCells(xlConstants,xlTextValues)
cell.Value = "'" & Application.Substitute(cell.Text,"X","0")
Next