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

 

[Peter_SSs]

Here is another macro that you can try...

Rick that might need another check - for blank cells - so a zero doesn't get introduced?

 

[skyport]

Rick, Tested the code and it works for column A but not columns B-G

 

[Rick Rothstein]

Whoops! I forgot about the other columns... sorry. Simple fix, but I am going to correct the more compact code I posted in Message #10 because it does not have the problem that Peter pointed out in Message #11 (for my longer code)...

[table="width: 500"]
[tr]
	[td]Sub RemoveLeadingSpaceColonOrZero()
  Dim Addr As String
  Addr = "A1:G" & 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]

 

[skyport]

Hi Peter. We certainly have a great group on here tonight. Good to see you again as well. We have the codes working to the degree as mentioned above. What I didn't count on was multiples of the specified characters at the beginning of the cell. For example, a cell might contain

: 09/22/2015


in that example there are 5 of the specified characters before the data to be retained being the following:


colon
3 spaces (site won't let me illustrate the 3 spaces between the colon and the 0 - so we'll pretend there are 3)
0


Using the current solutions presented, I could run the code multiple times (5) and eliminate each. Don't know if it is possible but if there were a way to eliminate all five of the specified characters with one pass or run of the code, that would be amazing, once again if it were possible simply because there may be as many is 50 or 60 different cells with multiple specified characters in front of other data that needs to be retained. One things for sure, from my experience, we've got the best of the best on this thread at the moment with great input so far. If this team can't make that last part work no one can.

 

[tygrrboi]

Like this?

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"
                While InStr(": 0",Left(Cells(r, c), 1)) <> 0
                    Cells(r, c) = Right(Cells(r, c), Len(Cells(r, c)) - 1)
                Wend
        End Select
    Next r
Next c

End Sub

 

[skyport]

tygrrboi You nailed it again. Works perfectly and gets them all out. Thanks so much.


Rick, the code in #13 works one at a time and for columns A-E but eliminates entire data in cells in G and replaces with # VALUE

 

[Peter_SSs]

You could also try the following. It should be somewhat faster as it is not looping through the actual worksheet cells one at a time and it also removes all the offending characters from a cell without that second looping action.

I've also assumed the number of rows can be determined from column A.

Sub RemoveLeadingCharacters()
  Dim a As Variant
  Dim RX As Object
  Dim i As Long, j As Long
  
  Set RX = CreateObject("vbScript.RegExp")
  RX.Pattern = "^[ :0]+"
  With Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Value
    For i = 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
        a(i, j) = RX.Replace(a(i, j), "")
      Next j
    Next i
    .Value = a
  End With
End Sub

 

[skyport]

Peter, As always, your code works perfect and fast as well. I think the Excel Team here hit a grand slam with the solutions presented. Thanks to all of you and look forward to next time

 

[Rick Rothstein]

You could also try the following. It should be somewhat faster as it is not looping through the actual worksheet cells one at a time and it also removes all the offending characters from a cell without that second looping action.

I've also assumed the number of rows can be determined from column A.

Sub RemoveLeadingCharacters()
  Dim a As Variant
  Dim RX As Object
  Dim i As Long, j As Long
  
  Set RX = CreateObject("vbScript.RegExp")
  RX.Pattern = "^[ :0]+"
  With Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Value
    For i = 1 To UBound(a, 1)
      For j = 1 To UBound(a, 2)
        a(i, j) = RX.Replace(a(i, j), "")
      Next j
    Next i
    .Value = a
  End With
End Sub

Although not really noticeable on human terms, the following non-RegExp macro ran a little faster on my computer (0.04 seconds versus 0.08 seconds for 5000 rows of data in Column A:G)...

Sub RemoveLeadingSpacesColonsAndZeroes()
  Dim R As Long, C As Long, Data As Variant
  With Range("A1:G" & Cells(Rows.Count, "A").End(xlUp).Row)
    Data = .Value
    For R = 1 To UBound(Data, 1)
      For C = 1 To UBound(Data, 2)
        Do While Left(Data(R, C), 1) Like "[ :0]"
          Data(R, C) = Mid(Data(R, C), 2)
        Loop
      Next
    Next
    .Value = Data
  End With
End Sub

 

[skyport]

Rick, thanks so much as always for your continued input. I will test the codes out later today and give you a report back later today.