Trimming leading character from string and replace underscore withdashes
 

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

Trimming leading character from string and replace underscore with dashes
 

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)



"andreashermle" wrote in message
...
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

Trimming leading character from string and replace underscorewith dashes
 

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

Trimming leading character from string and replace underscorewith dashes
 

On Apr 19, 8:14*pm, Wouter HM wrote:
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

Trimming leading character from string and replace underscorewith dashes
 

On Apr 19, 8:12*pm, "Rick Rothstein"
wrote:
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)

"andreashermle" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Rick,

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