Macro to convert specific Numbers to Letters...

  • Thread starter Thread starter Steven Ratliff
  • Start date Start date
S

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
 
Is this what you want?
Sub convert0()
If Left([m5], 1) = 0 _
Or Right([m5], 1) = 0 _
Then [m5] = "x"
End Sub
 
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
 
Back
Top