| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replace last character in string | Excel Programming | |||
| How to replace a formula string with * in the leading position? | Excel Worksheet Functions | |||
| more character 160 leading/trailing find/replace problems | Excel Discussion (Misc queries) | |||
| Replace font of one character in a string | Excel Programming | |||
| How replace a character in a string of characters? | Excel Programming | |||
Linear Mode
