Remove Leading Comma
 

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

Remove Leading Comma
 

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
'<<=============


---
Regards,
Norman



"Katie" wrote in message
...
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

Remove Leading Comma
 

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" wrote:

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

Remove Leading Comma
 

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.

"Tom Hutchins" wrote:

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" wrote:

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

Remove Leading Comma
 

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


"Katie" wrote in message
...
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

Remove Leading Comma
 

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" wrote in message
...
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.

"Tom Hutchins" wrote:

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" wrote:

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

Remove Leading Comma
 

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!

"PCLIVE" wrote:

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


"Katie" wrote in message
...
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

Remove Leading Comma
 

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" wrote:

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.

"Tom Hutchins" wrote:

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" wrote:

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

Remove Leading Comma
 

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!

"PCLIVE" wrote:

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" wrote in message
...
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.

"Tom Hutchins" wrote:

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" wrote:

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

Remove Leading Comma
 

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

Remove Leading Comma
 

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


"Norman Jones" wrote:

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
'<<=============


---
Regards,
Norman



"Katie" wrote in message
...
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