Help to remove ; trail from the middle of a string

[babbis77]

Hi,

I only find how to remove comma trails from the end of a string in VBA.
But i have them in the middle like this.

one;two;;;;three;four;

how do i clean it in VBA

so it looks like this

one;two;three;four;

 

[Rick Rothstein]

Rick

Re this code:

  For Each vNum In Array(13, 5, 3, 3, 2)
    txt = Replace(txt, String(vNum, ";"), ";")
  Next

I can see in the first iteration it builds a string of 13 semi colons, and replaces it with one.
Next iteration a string of 5 semi colons, replacing it with one, etc.

I'm interested in how you figure out the number sequence.

[edit]

it's OK, I see it is a Fibonacci sequence. [edit] Or is it?

The sequence is not original with me... it goes back to 2004 to a newsgroup thread for the compiled version of VB that I saw about replacing multiple contiguous internal spaces within a text string (that version of VB did not have the benefit of Excel's worksheet TRIM function)... the analysis was for spaces, but it holds for any character, so I applied it to this thread's semicolon question. Here is a link to the thread where those actual numbers came from...

https://groups.google.com/forum/#!topic/microsoft.public.vb.general.discussion/TqZHK9cPnpU

 

[hiker95]

Thank you guys! So many working ways

babbis77,

Thanks for the feedback.

You are very welcome. Glad we could help.

I hope you have had a chance to try my macro solution.

And, come back anytime.

 

[Rick Rothstein]

Sub ReplaceSemicolonDupes()
' hiker95, 04/09/2015, ME847584
With Range("A1", Range("A" & Rows.Count).End(xlUp))
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","";;;;;"","";""),"""")")
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","";;;;"","";""),"""")")
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","";;;"","";""),"""")")
  .Value = Evaluate("IF(ROW(),SUBSTITUTE(" & .Address & ","";;"","";""),"""")")
End With
End Sub

I would be willing to bet the OP will not run into this limit, but just letting you know, your code fails for 39, and fails spottily for more than 39, consecutive semicolons. I guess you are using the 5-4-3-2 pattern of repeated semicolons because it is easy to remember, however, your code would work up to the same 39 consecutive semicolon limit if you changed the pattern to 5-3-2-2 (and saved your fingers from having to type those two extra semicolons). Intersestingly, using the Evaluate function the way you did is three times faster than using the Range's built-in Replace method... I found that kind of surprising myself.

 

[hiker95]

Rick Rothstein,

The macro that I wrote was based on the raw data that babbis77 provided, and, I stated that in my reply.

If babbis77 had replied with more test examples, I would have adjusted the macro.

Intersestingly, using the Evaluate function the way you did is three times faster than using the Range's built-in Replace method... I found that kind of surprising myself.

Thanks for that.