vba code to Replace a varying-length string of identical characters

Hermac

New Member
Joined
Sep 5, 2016
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,
I know how to write VBA code in Excel but cannot cope with this.
I have a column with varying text in which de string "-----" appears frequently. The number of consecutive minuses signs may vary from 5 to more than 100. I want these strings either simply removed or replaced by just 3 concecutive minuses.
The series of minuses always occurs in this context : RAM:3-000061184934 -------------------------------------------------- Btw, where the number after RAM.3-varies but always has 12 digits.
Could you give me a helping hand?
Thank you very much.
Herman
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
Sub Repl()
    '3 consec ---
    While InStr(MyStr, "----") > 0
        MyStr = Replace(MyStr, "--", "-")
    Wend
    
    ' or delete all -
    MyStr = Replace(MyStr, "-", "")
    
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the "---" text always at the end of the cell, or can it be followed by other text?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
i wondered that too. makes Left( ) an easy option
 

Hermac

New Member
Joined
Sep 5, 2016
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,
I know how to write VBA code in Excel but cannot cope with this.
I have a column with varying text in which de string "-----" appears frequently. The number of consecutive minuses signs may vary from 5 to more than 100. I want these strings either simply removed or replaced by just 3 concecutive minuses.
The series of minuses always occurs in this context : RAM:3-000061184934 -------------------------------------------------- Btw, where the number after RAM.3-varies but always has 12 digits.
Could you give me a helping hand?
Thank you very much.
Herman
Thank you guys, it was simpler than I thought , with an asterisk between RAM.3 and Btw. Thank you anyway!
 

Forum statistics

Threads
1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top