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
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
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
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
That will not work correctly if there are more than two consecutive semicolons. However, this one would...Using a formula works
=SUBSTITUTE(A1,";;",";")
For Each vNum In Array(13, 5, 3, 3, 2)
txt = Replace(txt, String(vNum, ";"), ";")
Next
Excel 2007 | |||
---|---|---|---|
A | |||
1 | one;two;;;;three;four; | ||
2 | one;two;three;four; | ||
3 | one;;two;;three;;four;; | ||
4 | one;;;two;;;three;;;four;;; | ||
5 | one;;;;two;;;;three;;;;four;;;; | ||
6 | one;;;;;two;;;;;three;;;;;four;;;;; | ||
7 | one;;;;;;two;;;;;;three;;;;;;four;;;;;; | ||
8 | |||
Sheet1 |
Excel 2007 | |||
---|---|---|---|
A | |||
1 | one;two;three;four; | ||
2 | one;two;three;four; | ||
3 | one;two;three;four; | ||
4 | one;two;three;four; | ||
5 | one;two;three;four; | ||
6 | one;two;three;four; | ||
7 | one;two;three;four; | ||
8 | |||
Sheet1 |
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
Likeone;two;;;;three;four;