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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
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
36,729
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,302
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,722
Members
417,107
Latest member
derekMG

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