Trimming leading character from string and replace underscore withdashes

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

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
 
This code should do that for you...

Dim Cell As Range
......
......
For Each Cell In Selection
Cell.Value = Replace(Mid(Cell.Value, 2), "_", "-")
Next
 
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 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
Click to expand...

Hi,

that's it! Great! Thank you very much for your professional help.
Regards, Andreas
 
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 -
Click to expand...

Rick,

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