Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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 -- Gary''s Student - gsnu200800 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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 "CB" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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 "dmoney" wrote in message ... 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 "CB" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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 "dmoney" wrote in message ... 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 "CB" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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! -- Gary''s Student - gsnu200800 "CB" wrote: 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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" wrote in message ... 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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" wrote: 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
Hmm, I tried dmoney's code but it did not work, I'm at a loss as of right
now... "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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" wrote in message ... 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" wrote: 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
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)" wrote: 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" wrote in message ... 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
Did you see my latest posting? I gave you code to do what you wanted in it.
Rick "CB" wrote in message ... 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)" wrote: 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" wrote in message ... 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... "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200800 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating commas if they are first characters in a string
Thanks Rick -- I did not know that tid bit.
dmoney "CB" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count no. of commas (,) in a string in a cell | Excel Worksheet Functions | |||
String separated with commas | Excel Programming | |||
eliminating text between two specific characters | Excel Programming | |||
How can I break down a string with data divided by commas | Excel Programming | |||
removing commas from a string | Excel Programming |