Remove comma if it’s the first character in a string

[rex759]

The macro below removes the comma from the string but changes some of the information into a decmil.

For example, ,2536 8/22 becomes 2536 4/11. In the formula bar, it reads; 2536.36363636364.
It should be; 2536 8/22 (just without the comma)

I tried changing the Value2s to Value and then I remove it but nothing seems to do the trick.

With Worksheets("Sheet1")
	For i = 4 to 12
	    Do
	If Left$(.Cells(i, "C").Value2, 1) = ","  then
	    .Cells(i, "C").Value2 = Right$(.Cells(i, "C").Value2, Len(.Cells(i, "C").Value2) - 1)
              End if
	  Loop While Left$(.Cells(i, "C").Value2, 1) = ","
                  .Cells(i, "C").Value2=Ltrim(.Cells(i, "C").Value2)
         Next I
End with

Any help is appreciated.

Thank you

 

[JoeMo]

You may have to convert the string to a number stored as text:

With Worksheets("Sheet1")
    For I = 4 To 12
        Do
        If Left$(.Cells(I, "C").Value2, 1) = "," Then
            .Cells(I, "C").Value2 = "'" & Right$(.Cells(I, "C").Value2, Len(.Cells(I, "C").Value2) - 1)
        End If
        Loop While Left$(.Cells(I, "C").Value2, 1) = ","
    Next I
End With

 

[Rick Rothstein]

The macro below removes the comma from the string but changes some of the information into a decmil.

For example, ,2536 8/22 becomes 2536 4/11. In the formula bar, it reads; 2536.36363636364.
It should be; 2536 8/22 (just without the comma)

Your values after you remove the leading commas are in the format Excel uses to display fractions, so it is reducing the fractions to their lowest denominator. Here is code (untested) that should work. Notice that I have introduced a new variable named Txt... if you declare your variables (and I think it is a good practice to do so), then Dim the Txt variable as String. One reason I introduced the Txt variable is because your repeated direct references to the cell being processed is inefficient... minimizing your code's direct interaction with the cells in a worksheet is more efficient than continually referencing the cells directly. Also, using the variable stops Excel from being "helpful" and converting your data (to fractions in this case) every time you assign something to a cell's Value property. Here is the modified code snippet which I believe should work for you...

With Worksheets("Sheet1")
  For i = 4 To 12
    Txt = .Cells(i, "C").Value2
    Do
      If Left$(Txt, 1) = "," Then
        Txt = Right$(Txt, Len(Txt) - 1)
      End If
    Loop While Left$(Txt, 1) = ","
    .Cells(i, "C").NumberFormat = "@"
    .Cells(i, "C").Value2 = LTrim(Txt)
  Next i
End With

 

[shg]

    Do
      If Left$(Txt, 1) = "," Then
        Txt = Right$(Txt, Len(Txt) - 1)
      End If
    Loop While Left$(Txt, 1) = ","

Rick, I'm shocked, shocked ....

    Do While Left$(Txt, 1) = ","
      Txt = Mid$(Txt, 2)
    Loop

 

[rex759]

Thank you both! I used Rick's version because of his explanation of the cell reference. Worked perfectly. Thank you

 

[Rick Rothstein]

Rick, I'm shocked, shocked ....

    Do While Left$(Txt, 1) = ","
      Txt = Mid$(Txt, 2)
    Loop

Don't be... I only had three (actually, a little less) sleep last night. I wasn't even paying attention to the OP's code, just the problem... I'm thinking I didn't see the improvement due to the lack of sleep which I am guessing affected my "code improvement vision".