Trimming leading character from string and replace underscore withdashes

[andreashermle]

Dear Experts:

My worksheet has numerous entries, which all have the following form:

_##_###_##_## (e.g. _90_237_44_44 or _40_443_22_77)

I would like to run a macro that changes the SELECTED CELLS from
_##_###_##_## to ##-###-###-##

That is ...
.... the first underscore has to be dropped
.... all the other underscores have to be replaced with dashes.

I got a formula alternative but I'd rather run a macro that performs
the following on SELECTED CELLS:

SUBSTITUTE(MID(A1,2,LEN(A1)),"_","-")

Help is much appreciated. Thank you very muc in advance.

Regards, Andreas

 

[Rick Rothstein]

This code should do that for you...

Dim Cell As Range
......
......
For Each Cell In Selection
Cell.Value = Replace(Mid(Cell.Value, 2), "_", "-")
Next

 

[Wouter HM]

Hi Andreas,

You could try something like,

Sub UnderscoreToDash()
Dim r As Range

For Each r In Selection
r.Value = Replace(Mid(r.Text, 2), "_", "-")
Next
End Sub

HTH,

Wouter

 

[andreashermle]

Hi Andreas,

You could try something like,

Sub UnderscoreToDash()
 Dim r As Range

 For Each r In Selection
  r.Value = Replace(Mid(r.Text, 2), "_", "-")
 Next
End Sub

HTH,

Wouter

Hi,

that's it! Great! Thank you very much for your professional help.
Regards, Andreas

 

[andreashermle]

This code should do that for you...

Dim Cell As Range
.....
.....
For Each Cell In Selection
  Cell.Value = Replace(Mid(Cell.Value, 2), "_", "-")
Next

--
Rick (MVP - Excel)












- Show quoted text -

Rick,

Perfect. Thank you very much for your great help. Regards, Andreas