PDA

View Full Version : VBA to remove a comma if it's the cells first characters



daniels012
03-10-2011, 08:35 AM
I need a looping code that will remove a comma at the beginning of a cell all the way down column T

Something like this:
,aurtd
,breed ,sdvs ,dvsv
,aolikj ,zdv ,zdc
adca ,dddv ,dvvgg
bgfd ,tgd ,dfge
, adfs ,rks ,tyyo

Would become this:
aurtd
breed ,sdvs ,dvsv
aolikj ,zdv ,zdc
adca ,dddv ,dvvgg
bgfd ,tgd ,dfge
adfs ,rks ,tyyo



Thank You in advance!
Michael

Bob Phillips
03-10-2011, 08:41 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow

Do

If Left$(.Cells(i, "A").Value2, 1) = "," Then

.Cells(i, "A").Value2 = Right$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
End If
Loop While Left$(.Cells(i, "A").Value2, 1) = ","

.Cells(i, "A").Value2 = LTrim(.Cells(i, "A").Value2)
Next i
End With

Application.ScreenUpdating = True
End Sub

daniels012
03-10-2011, 09:01 AM
xld,
It seems you are always the one to help me!
I appreciate this greatly.
That worked perfect, same as the last macro you helped with.

OK,
How about this. I am finding that I need to put a space after each comma within the cells but if it has a space, then ignore it.
Like this:
aurtd
breed,sdvs,dvsv
aolikj,zdv, zdc
adca,dddv,dvvgg
bgfd,tgd,dfge
adfs, rks,tyyo
To this:
aurtd
breed, sdvs, dvsv
aolikj, zdv , zdc
adca, dddv, dvvgg
bgfd, tgd, dfge
adfs , rks, tyyo

Is this possible?

Michael

daniels012
03-10-2011, 09:19 AM
xld,
Nevermind, I can use find and replace to do this. I know it is not VBA , but I can do that one thing this time.

Thank You again,
Michael

shrivallabha
03-10-2011, 10:11 PM
I tried a variation. Here it is:
Sub SplitData()
Dim vData As Variant
Dim sData As String
Dim lLastRow As Long
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To lLastRow
vData = Split(Range("A" & i).Value, ",")
sData = ""
For j = LBound(vData) To UBound(vData)
If vData(j) <> vbNullString Then
If j <> UBound(vData) Then
sData = sData & Trim(vData(j)) & ", "
Else
sData = sData & Trim(vData(j))
End If
End If
Next j
Range("A" & i).Value = sData
Next i
End Sub

Bob Phillips
03-11-2011, 02:07 AM
I thoought I had replied with the VBA to do that last step, but it isn't here so I will post it anyway



Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow

Do

If Left$(.Cells(i, "A").Value2, 1) = "," Then

.Cells(i, "A").Value2 = Right$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
End If
Loop While Left$(.Cells(i, "A").Value2, 1) = ","

.Cells(i, "A").Value2 = LTrim(Replace(.Cells(i, "A").Value2, ",", ", "))
Next i
End With

Application.ScreenUpdating = True
End Sub