Help to remove ; trail from the middle of a string

babbis77

New Member
Joined
Nov 15, 2007
Messages
7
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;
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Using a formula works

=SUBSTITUTE(A1,";;",";")

however

Application.WorksheetFunction.Substitute

produces a different result
 
Upvote 0
This should work

Code:
Sub k1()
j = Range("A1").Value & " "
k = ""
For i = 1 To Len(j)
If Mid(j, i, 1) <> ";" Or Mid(j, i + 1, 1) <> ";" Then k = k & Mid(j, i, 1)
Next i
k = Left(k, Len(k) - 1)
Range("A2") = k
End Sub

Input in cell A1
Output in cell A2
 
Upvote 0
If you split the input string into an array it would look something like:
arrText(0) = "one"
arrText(1) = "two"
arrText(2) = ""
arrText(3) = ""
arrText(4) = ""
arrText(5) = "three"
arrText(6) = "four"

You can then loop through the arr, test to see if the value is not empty, and rebuild the string.
See the test() procedure below which calls the CleanText() function.

Rich (BB code):
Sub test()
   Dim txt As String
   
   txt = "one;two;;;;three;four;"
   
   txt = CleanText(txt)
   
   'output
   MsgBox txt
End Sub




Function CleanText(ByRef InputText As String) As String
   Dim arrText As Variant  'array to hold elements of input string
   Dim i As Long           'array loop index
   Dim tmp As String       'temp string holder
   
   arrText = Split(InputText, ";")
   For i = LBound(arrText) To UBound(arrText)
      If arrText(i) <> "" Then
         tmp = tmp & arrText(i) & ";"
      End If
   Next i
   
   CleanText = tmp
End Function
 
Upvote 0
I don't think you will find any faster routine at reducing multiple consecutive semicolons to single semicolons than this one...

Code:
Function OneSemiColon(Txt As String) As String
  Dim vNum As Variant
  For Each vNum In Array(121, 13, 5, 3, 3, 2)
    Txt = Replace(Txt, String(vNum, ";"), ";")
  Next
  OneSemiColon = Txt
End Function

The above function will handle up to 9840 consecutive semicolons, if you are sure you will never exceed 120 consecutive semicolons in a single text string, then you can use this instead (although the savings in time will be virtually zero by doing so)...

Code:
Function OneSemiColon(Txt As String) As String
  Dim vNum As Variant
  For Each vNum In Array(13, 5, 3, 3, 2)
    Txt = Replace(Txt, String(vNum, ";"), ";")
  Next
  OneSemiColon = Txt
End Function
 
Last edited:
Upvote 0
Using a formula works

=SUBSTITUTE(A1,";;",";")
That will not work correctly if there are more than two consecutive semicolons. However, this one would...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,REPT(";",121),";"),REPT(";",13),";"),";;;;;",";"),";;;",";"),";;;",";"),";;",";")
 
Last edited:
Upvote 0
Rick

Re this code:
Rich (BB 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?
 
Last edited:
Upvote 0
babbis77,

If I understand you correctly.

Here is a macro solution (based on the raw data that you have posted) for you to consider (that does not do any looping thru the cells in column A), and, does not need any formulae, or, functions, copied in another column?

Sample raw data:


Excel 2007
A
1one;two;;;;three;four;
2one;two;three;four;
3one;;two;;three;;four;;
4one;;;two;;;three;;;four;;;
5one;;;;two;;;;three;;;;four;;;;
6one;;;;;two;;;;;three;;;;;four;;;;;
7one;;;;;;two;;;;;;three;;;;;;four;;;;;;
8
Sheet1


And, after the macro:


Excel 2007
A
1one;two;three;four;
2one;two;three;four;
3one;two;three;four;
4one;two;three;four;
5one;two;three;four;
6one;two;three;four;
7one;two;three;four;
8
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReplaceSemicolonDupes macro.
 
Last edited:
Upvote 0
Just out of curiosity...

Is this a result of a concatenation function?
one;two;;;;three;four;
Like
=CONCATENATE(A1,";",B1,";",C1,";",D1,";",etc.)
And the consecutive ;; occur because of blanks in those cells?

If so, can you show the code that produced that result?
Perhaps it can/should be adjusted to not do that in the first place, rather than 'fixing' it afterwards..
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top