Remove the first character of a string if is a hyphen '-` or equal sign = with VBA

[userxyz777]

I need to remove the hyphens and equal signs from cell values of a sheet (with lots of data) if they are the first character of the string

Cells look like
=Bob - Carol
-Ted - Alice

Then I need

Bob - Carol
Ted - Alice

I have tried many macros to do this and I can get non of them to work
Here is one of them. it gives an empty output

I tried to upload a Mimi-sheet but all I got was the chance to download a plugin

Thanks in advance for help with this

Sub removeTest()
Dim ws As Worksheet
Dim arr As Variant, v
Dim rng As Range
Dim LR As Long, LC As Long, i As Long, j As Long
  
  Set ws = ThisWorkbook.Sheets("Elements")
  
  If WorksheetFunction.CountA(ws.UsedRange) <> 0 And ws.UsedRange.Address <> "$A$1" Then
  
   With ws
    LC = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    LR = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    
    Set rng = .Range("A" & startrow).Resize(LR, LC)
    arr = rng.Value2
    
    'Remove "=" if first char.  Add more if needed
    For i = LBound(arr) To UBound(arr)
      For j = LBound(arr, 2) To UBound(arr, 2)
        arr(i, j) = RemoveFirstChar(CStr(arr(i, j)), "=-", 2)
        arr(i, j) = RemoveFirstChar(CStr(arr(i, j)), "=", 1)
        arr(i, j) = RemoveFirstChar(CStr(arr(i, j)), "-", 1)
      Next j
    Next i
    
  Debug.Print "Array of Keys:"
  For Each v In arr
      Debug.Print v
  Next
    
  ThisWorkbook.Sheets("A").Cells.Clear
  ThisWorkbook.Sheets("A").Range("A1").Resize(UBound(arr), UBound(arr, 2)) = arr

End Sub

Public Function RemoveFirstChar(RemFstChar As String, what As String, L As Long) As String
Dim TempString As String
TempString = RemFstChar
If InStr(RemFstChar, what) = 1 Then
'MsgBox RemFstChar
    TempString = Right(RemFstChar, Len(RemFstChar) - 1)
'MsgBox TempString
End If
End Function

 

[Rick Rothstein]

Does this standalone macro do what you want...

Sub RemoveLeadingMinusEqualSigns()
  Dim R As Long, StartRow As Long, Arr As Variant
  StartRow = 2
  Arr = Range(Cells(StartRow, "A"), Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Arr)
    Arr(R, 1) = Mid(Arr(R, 1), 1 - (InStr("=-", Left(Arr(R, 1), 1)) > 0))
  Next
  Cells(StartRow, "A").Resize(UBound(Arr)) = Arr
End Sub

 

[userxyz777]

Sub RemoveLeadingMinusEqualSigns() Dim R As Long, StartRow As Long, Arr As Variant StartRow = 2 Arr = Range(Cells(StartRow, "A"), Cells(Rows.Count, "A").End(xlUp)).Value For R = 1 To UBound(Arr) Arr(R, 1) = Mid(Arr(R, 1), 1 - (InStr("=-", Left(Arr(R, 1), 1)) > 0)) Next Cells(StartRow, "A").Resize(UBound(Arr)) = Arr End Sub

Hello Rick, I get Type mismatch for R=1
`Arr(R, 1) = Mid(Arr(R, 1), 1 - (InStr("=-", Left(Arr(R, 1), 1)) > 0))`

If I hover over Arr(R, 1) I get Error 2029

 

[Rick Rothstein]

Where is your existing data located at (sheet name and range)?

 

[bebo021999]

Try:

Option Explicit
Sub RemoveText()
Dim cell As Range
For Each cell In Worksheets("Element").UsedRange
   If cell Like "-*" Then cell.Value = Mid(cell, 2, 255)
   If cell Like "=*" Then cell.Value = Mid(cell, 2, 255)
Next
End Sub

 

[userxyz777]

I finally got this to work. Thanks for the help Rick

Sub RemoveLeadingMinusEqualSigns()
  Dim R As Long, StartRow As Long, arr As Variant, sTemp As String, v
  StartRow = 1
 
  arr = Range(Cells(1, "A"), Cells(Rows.count, "A").End(xlUp)).value
 
'You need to run this 2x, first with Error handing and the without
  sTemp = ""
  For R = 1 To UBound(arr)
   If CLng(arr(R, 1)) = CLng(CVErr(2029)) Then
     sTemp = Trim(Cells(R, 1).text)
     Do While Left(sTemp, 1) = "=" Or Left(sTemp, 1) = "-" Or Left(sTemp, 1) = "."
              sTemp = Trim(Mid(sTemp, 2))
     Loop
 
    arr(R, 1) = sTemp
    sTemp = vbNullString
   End If
  Next
 
'You need to run this 2x, first with Error handing and the without
  For R = 1 To UBound(arr)
     sTemp = Trim(Cells(R, 1).text)
     Do While Left(sTemp, 1) = "=" Or Left(sTemp, 1) = "-" Or Left(sTemp, 1) = "."
              sTemp = Trim(Mid(sTemp, 2))
     Loop
 
    arr(R, 1) = sTemp
    sTemp = vbNullString
  Next

  Debug.Print "Array of Keys:"
  For Each v In arr
      Debug.Print v
  Next
 
 
  Cells(StartRow, "A").Resize(UBound(arr)) = arr
End Sub