2

I have a column, which has a comma separated values inside each cell that look like this

0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0
1.5, 1.6,2.0, 10.6,10.9, 15.2,30.75
20, 0.25,280.2, 0.29,300.2, 423,530.76

Like a text string.

The goal is to remove the leading zero in front of the decimal, but only when there is no other digit (including another 0) in front of it I use the search replace function vba:

    Option Explicit
    Public Sub Replace0dot(Optional byDummy As Byte)
        Columns("A").Replace What:"0.", _
                            Replacement:=".", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
    Application.ScreenUpdating = True
    End Sub 

and I end up with this:

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 1.6,1.9, 15.2,3.75
2, .25,28.2, .29,30.2, 423,53.76

It removes all instances of leading 0. with ., so you see 10.6 becomes 1.6. But it should remain 10.6 How can I get a search replace equivalent that gives me:

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 10.6,10.9, 15.2,30.75
20, .25,280.2, .29,300.2, 423,530.76

??? Seems like there would have to be un-concatenate and re-concatenate to achieve the goal.

  • Split at . Check the content before . For length or check individual digital if it's and then based on location remove – SeanClt Apr 25 '16 at 7:14
  • Alternatively to Split you can also use regular expressions to look for ([^0-9])0\. an replace to $1.. You can find plenty of tutorials on the net on using regular expressions in VBA. – Máté Juhász Apr 25 '16 at 7:15
  • @Mate, @SeanClt split or regular expressions these may work. Did either of you intend to write a answer? I would assume there would have to be some if/then to distinguish between 0. and ([^0-9])0\.. I'm not a programmer, although I try to be logical when writing most of the time. – Jon Grah Apr 25 '16 at 8:24
2

Here is a very simple approach:

  • if the string begins with 0. then drop the zero
  • if the string contains triplets like {space}0. then drop that zero
  • if the string contains triplets like ,0. then drop that zero

Select the cells and run this code:

Sub fixdata()
    Dim r As Range, t As String

    For Each r In Selection
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t
    Next r
End Sub

before:

enter image description here

and after:

enter image description here

If there are other triplets that must be changed, just add another Replace()

EDIT#1:

To avoid manual selection of the cells, we can have the macro do it.........here is an example for column A:

Sub fixdata2()
    Dim r As Range, t As String

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t
    Next r
End Sub

EDIT#2

In this version we append a ; to the end of each cell just before entering text into that cell:

Sub fixdata3()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        r.Value = t & Suffix
    Next r
End Sub

EDIT3#:

In this version the ; is appended only if it not already present in the cell:

Sub fixdata4()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If Left(t, 2) = "0." Then t = Mid(t, 2)
        t = Replace(t, " 0.", " .")
        t = Replace(t, ",0.", ",.")
        If Right(t, 1) <> Suffix Then
            r.Value = t & Suffix
        End If
    Next r
End Sub

EDIT#4:

This version will not affect empty cells:

Sub fixdata5()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If t <> "" Then
            If Left(t, 2) = "0." Then t = Mid(t, 2)
            t = Replace(t, " 0.", " .")
            t = Replace(t, ",0.", ",.")
            If Right(t, 1) <> Suffix Then
                r.Value = t & Suffix
            End If
        End If
    Next r
End Sub

EDIT#5:

This fixes the bug in the previous version:

Sub fixdata6()
    Dim r As Range, t As String, Suffix As String
    Suffix = ";"

    For Each r In Intersect(Range("A:A"), ActiveSheet.UsedRange)
        t = r.Text
        If t <> "" Then
            If Left(t, 2) = "0." Then t = Mid(t, 2)
            t = Replace(t, " 0.", " .")
            t = Replace(t, ",0.", ",.")
            If Right(t, 1) <> Suffix Then
                t = t & Suffix
            End If
            r.Value = t
        End If
    Next r
End Sub
  • Is there a way to highlight the cells within the code vs manually highlighting? – Jon Grah Apr 25 '16 at 22:12
  • @JonGrah Yes...............I will update the code to give an example. – Gary's Student Apr 25 '16 at 23:29
  • 1
    @JonGrah See my EDIT#1 – Gary's Student Apr 25 '16 at 23:34
  • Upvoted. This is a very simple approach and it appears to work every time I run it. Here is extra credit: How would I add a semicolon ; to the end of each row? e.g. 0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0 becomes .1, .2,.3, .4,.5, .8,1.0; ?? – Jon Grah Apr 26 '16 at 5:33
  • 1
    @JonGrah See my EDIT#2 – Gary's Student Apr 27 '16 at 13:11
0

Use this VBA code it will test every string for leading zeros

Sub Replace0dot()

   Dim str As String    
   Dim ln As Long   
   Dim i As Long    

   ln = Range("A1").End(xlDown).Row   
   For i = 1 To ln
   str = Cells(i, 1).Value
   If Left(str, 1) = "0" Then
   Cells(i, 1) = Mid(str, 2)
   End If
   Next i

End Sub   
  • 1) Does not work fully when I attempt to run module (Excel 2010). It only removed the first 0 from the first 0. in the first cell, not the whole string and not for all the cells in column A. 2) Can you add some comments to your answer describe what parts of your code does what? Let's say I wanted to start with A2. Is there anything else that needs to be adjusted. Is i defining each character to count after each comma , the string? – Jon Grah Apr 25 '16 at 21:09
0

Assuming you're still working with lines from notepad++ you can use an array instead of text to columns

Sub notepadthingrevisit()
    Dim workingRange As Range
    Set workingRange = Range("A1:A3")
    Dim i As Long
    Dim j As Long
    Dim result As String

    Dim myStrings() As String
    For i = 1 To workingRange.Rows.Count
        myStrings = Split(Cells(i, 1), ",")
        'Adjust this for accounting for the first value and remember to trim the " " at the end
        For j = 0 To UBound(myStrings)
            If Left(Trim(myStrings(j)), 1) = 0 Then
                myStrings(j) = Right(Trim(myStrings(j)), (Len(Trim(myStrings(j))) - 1))
            End If
            If myStrings(j) = Int(myStrings(j)) Then myStrings(j) = Int(myStrings(j))
            'Check here for j mod x and insert " "
            result = result & myStrings(j) & ","
        Next
        Cells(i, 5) = result
    Next

End Sub

Just use the information from the previous thread to insert your spaces.

  • Does NOT seem to work. I wasn't sure if it was to replace notepadthing() or run after . I tried both and it appears to attempt to replace notepadthing(). What it does is create a pyramid in column E. I had to widen the column so that you can see it in this screenshot . – Jon Grah Apr 26 '16 at 6:27
  • Gary's Student fixzero(), works as advertised. So after running notepadthing(), It works beautifully. You are welcome to take another stab at it. – Jon Grah Apr 26 '16 at 6:53
  • This was instead of the notepad thing, but if you got what you need - great. – Raystafarian Apr 26 '16 at 9:07
  • ok. But I am curious to know you went from a perfect solution with the notepadthing() and this solution creates the pyramid result. Did you see the screenshot? – Jon Grah Apr 27 '16 at 0:51
  • No I didn't see the pyramid – Raystafarian Apr 27 '16 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.