Eliminating commas if they are first characters in a string

[CB]

I have written a bit of code to delete a comma if it happens to be the first
character in a string, however my code seems to be deleting all commas, I am
a VB beginner can anyone help me out?

What I thought my code did:
If have ",,Hello, my, name,,, is" in a cell before I run the code, after I
run the code I would like the cell to look like "Hello, my, name,,, is"


What my code seems to do:
Output: "Hello my name is"

This is my code (I have pieced it together from looking at the forum so I
may not fully understand what is going on):

Sub ahhhhhhhh()

For Each cell In Range("H59:H60")
If InStr(1, cell.Value, ",", vbTextCompare) = 1 Then
cell.Value = Replace(cell.Value, ",", "", 1, -1, vbTextCompare)
End If
Next

End Sub

 

[Gary''s Student]

Sub ahhhhhhhh()

For Each cell In Range("H59:H60")
While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Wend
Next

End Sub

 

[dmoney]

This should do it

For Each cell In Range("H59:H60")
If InStr(1, cell.Value, ",", vbTextCompare) = 1 Then
cell.Value = Mid(cell.Value, 2, 50)
End If
Next

 

[Rick Rothstein \(MVP - VB\)]

If InStr(1, cell.Value, ",", vbTextCompare) = 1 Then

Regarding the above line from you posted code... when vbTextCompare is
specified, the InStr function has extra work to do and is consequently
slower. Since a comma has no case (upper/lower), there is no reason to
burden the InStr function with the extra work... the 4th parameter can be
omitted (it will then default to the quick vbBinaryCompare) and, since you
are omitting the 4th argument and starting your search at character 1, you
can omit the first argument as well (it's optional and defaults to 1 if
omitted). So, I would suggest using this instead...

If InStr(cell.Value, ",") = 1 Then

Rick

 

[CB]

Thanks everyone for the quick responces!

Gary"s Student could you explain to me what this part of the code is doing?

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

I have not seen Wend before...

 

[Rick Rothstein \(MVP - VB\)]

cell.Value = Mid(cell.Value, 2, 50)

One more comment... unlike the worksheet version of Mid, the third argument
in the VB Mid function is optional... if you omit it, the remainder of the
text will be returned. So, instead of guessing at how many characters past
the second position you need to specify (the 50 in your function call
above), you can just do this and let VB get the remainder of the text for
you...

cell.Value = Mid(cell.Value, 2)

Rick

 

[Gary''s Student]

WHILE
WEND
are part of a loop.

We are saying that if the very first character is a comma, slice it off the
string. Then look at the next character.

We continue until ALL leading commas are removed!

 

[Rick Rothstein \(MVP - VB\)]

While is an older non-For...Next loop construct and its loop "ender" is the
Wend statement. I think the 'end' part of Wend is meant to indicate "the end
of the loop" and the 'W' in front of it is supposed to link it to the
"While" statement (While-end...Wend). While a lot of people still use
While...Wend constructions, many now use Do While...Loop constructions
instead. Since, as Gary"s Student used it, the loop either executes once or
it doesn't execute at all, I think a simple If...Then block would be a
clearer way to implement this.

Rick

 

[CB]

I just noticed one issue, sometimes I may have multiple commas before any
actual text and I need all of the commas removed.

For example:
",,,,Hello,,,,My,"
Would need to be
"Hello,,,,My,"

Is there a way to modify your code so it can work?

 

[CB]

Hmm, I tried dmoney's code but it did not work, I'm at a loss as of right
now...

 

[Rick Rothstein \(MVP - VB\)]

Give this code a try...

Sub ahhhhhhhh()
Dim X As Long
Dim Cell As Range
Dim Text As String
For Each Cell In Range("H59:H60")
Text = Cell.Value
For X = 1 To Len(Text)
If Mid(Text, X, 1) <> "," Then Exit For
Next
Cell.Value = Mid(Text, X)
Next
End Sub

Rick

 

[CB]

I got it figured out, I went ahead and used a Do While like you wrote about
and that seems to haved worked out.

Thanks for the help!

 

[Rick Rothstein \(MVP - VB\)]

Did you see my latest posting? I gave you code to do what you wanted in it.

Rick

 

[dmoney]

Thanks Rick -- I did not know that tid bit.

dmoney