VBA: How to remove all extra spaces or a given character in the beginning and end of all cell data?

[azov5]

1. VBA: How to remove all extra spaces in the beginning and end of all cell data in a sheet ?
2. VBA: How to remove a given character in the beginning and end of all cell data ?
3. VBA: Remove cell data if no text charterer found.

 

[JLGWhiz]

1. VBA: How to remove all extra spaces in the beginning and end of all cell data in a sheet ?
2. VBA: How to remove a given character in the beginning and end of all cell data ?
3. VBA: Remove cell data if no text charterer found.

1.

With Sheets(1)
    .Range("A1") = Trim(.Range("A1").Value
End With

2.

With Sheets(1)
    .Range("A1") = Mid(.Range("A1").Value, 2, Len(.Range("A1").Value) - 2)
End With

3.

With Sheets(1)
    For i = 1 To Len(.Range("A1").Value)
        If Not Mid(Range("A1").Value, i, 1) Like "[A-Z, a-z]" Then
            .Range("A1").ClearContents
            Exit For
        End If
    Next
End With

 

[azov5]

Thanks JLGWhiz
2. VBA: How to remove a given character in the beginning and end of all cell data ?

I want to remove comma's "," if found in the beginning or end of all cell data.

 

[JoeMo]

Thanks JLGWhiz
2. VBA: How to remove a given character in the beginning and end of all cell data ?

I want to remove comma's "," if found in the beginning or end of all cell data.

Maybe:

Sub RemoveLeadingOrTrailingComma()
'select cells to alter then run this macro
Dim c As Range
For Each c In Selection
    c.Value = Replace(c.Value, ",", "", 1, 1)
    If InStr(Len(c.Value), c.Value, ",") > 0 Then
        c.Value = Left(c.Value, Len(c.Value) - 1)
    End If
Next c
End Sub

 

[Peter_SSs]

    c.Value = Replace(c.Value, ",", "", 1, 1)

Joe, that will remove the first comma in the cell, even if that comma is not "in the beginning of the data" in the cell.


Two suggestions for problem 2. I'd probably go with the second one in this case as I think it is a bit more robust.

Sub RemoveLeadingOrTrailingCharacter_v1()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long
  
  Const myCharacter As String = ","
  Set RX = CreateObject("VBSCript.RegExp")
  RX.Global = True
  RX.Pattern = Replace("(^\#)|(\#$)", "#", myCharacter)
  a = ActiveSheet.UsedRange.Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      a(i, j) = RX.Replace(a(i, j), "")
    Next j
  Next i
  ActiveSheet.UsedRange.Value = a
End Sub


Sub RemoveLeadingOrTrailingCharacter_v2()
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long
  
  Const myCharacter As String = ","
  a = ActiveSheet.UsedRange.Value
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    For j = 1 To uba2
      If a(i, j) Like myCharacter & "*" Then a(i, j) = Mid(a(i, j), 2)
      If a(i, j) Like "*" & myCharacter Then a(i, j) = Left(a(i, j), Len(a(i, j)) - 1)
    Next j
  Next i
  ActiveSheet.UsedRange.Value = a
End Sub

3. VBA: Remove cell data if no text charterer found.

Can you define exactly what you mean by a "text character"? To help clarify could you give 2 or 3 examples of cell contents that should be cleared and the same for those that should be kept?

 

[JoeMo]

Joe, that will remove the first comma in the cell, even if that comma is not "in the beginning of the data" in the cell.

Good catch Peter!