EXCEL VBA CODE for replace duplicate semi colon in single cell

[Jackyk1994]

I have many data in Cell A3 to A lastrow

For example like this:
abc;;;ab;;ccc;;;;;;;wwwwww

I want the result to replace at the same cell like this:
abc;ab;ccc;wwwwww

The quantity of semicolons are unknown

Tried a lot of AI generated results but failed, i am a beginner in VBA, thank you for your help

 

[JEC]

How about

Sub jec()
 Dim ar, j As Long
 ar = Range("A3", Range("A" & Rows.Count).End(xlUp))
 With CreateObject("vbscript.regexp")
  For j = 1 To UBound(ar)
    .Global = True
    .Pattern = ";(?=;)"
    ar(j, 1) = .Replace(ar(j, 1), "")
  Next
  Range("A3", Range("A" & Rows.Count).End(xlUp)) = ar
 End With
End Sub

 

[Fuji]

try

Sub test()
    Dim a, x, i&, ii&, n&
    With Range("c3", Range("c" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        For i = 1 To UBound(a, 1)
            x = Split(a(i, 1), ";"): n = 0
            For ii = 0 To UBound(x)
                If x(ii) <> "" Then x(n) = x(ii): n = n + 1
            Next
            If n Then
                ReDim Preserve x(n - 1)
                a(i, 1) = Join(x, ";")
            End If
        Next
        .Value = a
    End With
End Sub

abc;;;ab;;ccc;;;;;;;wwwwww;;;;
abc;;;ab;;ccc;;;;;;;wwwwww
abc;;;ab;;ccc;;;;;;;wwwwww
abc;;;ab;;ccc;;;;;;;wwwwww
abc;;;ab;;ccc;;;;;;;wwwwww
abc;;;ab;;ccc;;;;;;;wwwwww

To

abc;ab;ccc;wwwwww
abc;ab;ccc;wwwwww
abc;ab;ccc;wwwwww
abc;ab;ccc;wwwwww
abc;ab;ccc;wwwwww
abc;ab;ccc;wwwwww

 

[Cubist]

A formula option.

Book3
	A	B
1	abc;;;ab;;ccc;;;;;;;wwwwww	abc;ab;ccc;wwwwww
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(";",,TEXTSPLIT(A1,";",,1))

 

[JEC]

or even

Sub jecc()
 Dim j As Long
 For j = 1 To 10
  Range("A3", Range("A" & Rows.Count).End(xlUp)).Replace ";;", ";"
 Next
End Sub

 

[Georgiboy]

Another VBA option:

Sub test()
  Dim ws As Worksheet, rRng As Range
  
  Set ws = Sheets("Sheet1")
  Set rRng = ws.Range("A3:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)
  rRng = Evaluate("SUBSTITUTE(TRIM(SUBSTITUTE(" & rRng.Address(, , , 1) & ","";"","" "")),"" "","";"")")
End Sub

 

[Jackyk1994]

Thank you for the help, my problem has been solved