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

 

[skyport]

Rick,

Your final code as usual works like a charm.

 

[skyport]

To all 3:

Tygrrboi
Peter
Rick

At this point, as all 3 concepts work from all 3 sources, it will be interesting to see the difference in speed as they are applied to the higher volumes of data. I want to say thanks again for all the help from all three of you. This was one of the best collective inputs I have experienced from the greats here on Mr. Excel. I was a beautiful whirlwind of solutions.

 

[Peter_SSs]

... 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)...

That's a lot faster! 50%, or 100%, depending on which way you look at it.

If we did have huge data and needed speed, then I thought a little more could be eked out of your code by not interacting with the array as often during the Do loop, nor chopping the string as much, nor writing back to the array if the value has not changed.

Sub RemoveLeadingSpacesColonsAndZeroes_v2()
  Dim r As Long, c As Long, Data As Variant, i As Long, s As String
  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)
        s = Data(r, c)
        i = 1
        Do While Mid(s, i, 1) Like "[ :0]"
          i = i + 1
        Loop
        If i > 1 Then Data(r, c) = Mid(s, i)
      Next
    Next
    .Value = Data
  End With
End Sub

I guess it depends on the data too - how many cells have the leading characters and how many of those unwanted characters are at the start of the text - but for my samples, the average improvement (tested 20 or so times) was close to 10%.

However, I then went and ate my dinner and when I came back to post my results I thought that I would just test one more time. This time, and subsequently several more times I got almost identical times for the two codes.

So, just wondering if you want to bother testing to see what happens? I don't really care as the OP has a sufficient result & I'm just mucking around with it for fun.

 

[Rick Rothstein]

...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)...

That's a lot faster! 50%, or 100%, depending on which way you look at it.

You left out the beginning from the line you quote from me... "Although not really noticeable on human terms"... 0.04 seconds difference, while half the speed, would never be noticeable to a human observer... it was that which I was referring to when I said my macro ran "a little faster" than yours.

If we did have huge data and needed speed, then I thought a little more could be eked out of your code by not interacting with the array as often during the Do loop, nor chopping the string as much, nor writing back to the array if the value has not changed.

Not a bad thought, but even though the array is a Variant, I am not sure writing the array value to a String variable, processing it and then writing the value back to the array would save all that much time over simply working with the array directly.

I guess it depends on the data too - how many cells have the leading characters and how many of those unwanted characters are at the start of the text - but for my samples, the average improvement (tested 20 or so times) was close to 10%.

True... we appear to be talking about one or two, sometimes three, leading "bad" characters... a 10% improvement is a lot more than I would have expected.

However, I then went and ate my dinner and when I came back to post my results I thought that I would just test one more time. This time, and subsequently several more times I got almost identical times for the two codes.

That may actually prove my point... perhaps computer processes that run in the background are responsible for the difference.

So, just wondering if you want to bother testing to see what happens? I don't really care as the OP has a sufficient result & I'm just mucking around with it for fun.

Nah, while I understand "mucking around for fun", I really don't think it is worth the effort. Consider... with an execution speed of 0.04 seconds for 5000 rows, and assuming the 10% improvement figure you got originally, we are only talking about a maximum of an additional 0.84 seconds total execution time if all 1,048,576 rows were filled with data. In "human terms", this is not really all that significant that it would require worrying about.

 

[skyport]

Just following your postings guys and awesome opportunity for me to see the two Mr. Excel participants that I have seen resolve more issues for me over the past year. Awesome efforts and thanks to you both.