Need VBA to eliminate specific characters in cells

[skyport]

Hoping someone can help with this one.


In columns A through G, I need a VBA code that will eliminate the first character only in each cell and only when that first character in the cell is a blank space or a colon ( : )or 0 (zero-numerical). However, all other cells would remain unchanged.


Please help if you can

 

[SpreedsheetCrusader]

Sub REMOVESTUFF()

Dim tempstring As String
For Each cell In Sheets("sheet1").UsedRange
    
    tempstring = cell.Value
    If Left(cell, 1) = ":" Or Left(cell, 1) = " " Or Left(cell, 1) = 0 Then
        
    cell.Value = Right(tempstring, Len(tempstring) - 1)
    
    End If
    
Next cell


End Sub

 

[tygrrboi]

Another alternative.

Sub GetThoseNastyCharactersOuttaHere()
Dim c, r As Integer

For c = 1 To 7
    For r = 1 To 100    'I'm searching 100 rows, you can change this number to as many rows as you have data
        Select Case Left(Cells(r, c), 1)
            Case " ", ":", "0"
                Cells(r, c) = Right(Cells(r, c), Len(Cells(r, c)) - 1)
        End Select
    Next r
Next c

End Sub

 

[skyport]

Hi and thanks for taking time to help me. I ran the code but nothing happens. Any ideas?

 

[tygrrboi]

How are you calling this macro?

Is there a command button?

Do you want it do automatically happen whenever a cell in the worksheet is changed?

 

[skyport]

hi tygrrboi. my comment above was in reference to the first idea that was posted. Your code may be working. I am running it now and testing. Will have feedback in 15 min. Thanks again for helping

 

[skyport]

hi tygrrboi. got it to work and it is perfect thanks. Small detail that delayed me was a syntax involving a blank space after End Sub. When I removed it , all went well. Thanks again for the great solution.

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub RemoveLeadingSpaceColonOrZero()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF((LEFT(@)="" "")+(LEFT(@)="":"")+(LEFT(@)=""0""),MID(@,2,99),@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

 

[skyport]

Rick, Always great to see your input. Will test your code as well and give feedback asap

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub RemoveLeadingSpaceColonOrZero()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF((LEFT(@)="" "")+(LEFT(@)="":"")+(LEFT(@)=""0""),MID(@,2,99),@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]

While the above works (for me in my testing at least), so does this slightly more compact macro...

[table="width: 500"]
[tr]
	[td]Sub RemoveLeadingSpaceColonOrZero()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(ISERROR(FIND(LEFT(@),"" :0"")),@,MID(@,2,99))", "@", Addr))
End Sub[/td]
[/tr]
[/table]