Remove Leading Comma

[Katie]

Hello all- I know that we spoke before about removing a trailing comma, so i
tried to amend that code to remove a leading comma from a range, but it's not
working in cells where there's anything after the comma. Could anyone point
out what I did wrong with this code?

Range("I3:I10").Select
For Each cell In Selection
If Left(cell.Value, 1) = "," Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
End If
Next

 

[Norman Jones]

Hi Katie,

Try:

'=============>>
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = ActiveSheet.Range("I3:I10")

For Each rCell In Rng.Cells
With rCell
If Left(.Value, 1) = "," Then
.Value = Mid(.Value, 2)
End If
End With
Next rCell
End Sub
'<<=============

 

[Tom Hutchins]

If you want to remove a leading comma, you will need the RIGHT function
instead of the LEFT function:

cell.Value = Right(cell.Value, Len(cell.Value) - 1)

Hope this helps,

Hutch

 

[Katie]

Even if I want to remove the comma at the beginning of the cell?

I'm sorry- i thought leading was beginning and trailing was ending. I had
used the Right function for the end of the cell.

 

[PCLIVE]

You needed to use Right instead of left.

For Each cell In Range("I3:I10")
If Left(cell.Value, 1) = "," Then
cell.Value = Right(cell, Len(cell.Value) - 1)

End If
Next

 

[PCLIVE]

The reason for this is because you were returning a number of characters
starting from the Left. This means that Len(cell) gives you the total
number of characters including spaces from the active cell. Let's say it
was 10. So then you subtract 1 to make it 9. Then your returning 9
characters starting from the Left. That makes one character disapper from
the right. Using the same method but instead using the Right command, you
are returning 9 characters beginning from the right which drops the first
character.

HTH,
Paul

 

[Katie]

I get it I think! I was trying to change both the command and the "if"
part and that won't work! Thank you all for your help!

 

[Tom Hutchins]

The RIGHT function keeps as many characters as you specify from the right (or
end) of a string (the cell's value, in your case). You want to keep all the
characters except the leftmost one (the comma to be removed).

If E10 contains ,12345
Then =RIGHT(E10,LEN(E10)-1) in another cell will return 12345

Hope this helps,

Hutch

 

[Katie]

Paul, Tom and Norman,

Thank you for your help- you guys have been great!

It's really good to know a community like this exists!

 

[GS]

Hi Katie,

Try this:

For Each Cell in Range("I3:I10")
If Left$(Cell.Value) = "," Then Cell.Value = Mid$(Cell.Value, 2)
Next Cell

HTH
Regards,
Garry

 

[GS]

Sorry Norman, either I didn't see your post or I didn't scroll down far enough.