How to remove text after last comma, only if cell has 3 commas
Category: [Excel] Demo Available
How to remove text after last comma, only if cell has 3 commas
Requirement:
The user has the following macro which combs through column E and successfully removes any text after the last comma in a cell, but what I would like to know is if this can be modified so that it will only alter cells in E that contain three commas? In other words, if the cell only has one or two commas, the macro would ignore the cell and move on to the next. Alternatively, can it be modified so that it only applies to filtered, visible rows?
Code:
Dim LastRow As Long
Dim Pos As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 1 To LastRow
Pos = InStrRev(.Cells(i, "E").Value, ",")
If Pos > 0 Then
.Cells(i, "E").Value = Left(.Cells(i, "E").Value, Pos - 1)
End If
Next i
End With
Solution:
Change references as or if required.
Code:
Sub DeleteTail_By_cytop()
Dim LastRow As Long, i As Long
Dim v As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
v = Split(.Cells(i, "E").Value, ",")
If UBound(v) > 2 Then
.Cells(i, "F").Value = v(0) & "," & v(1) & "," & v(2)
End If
Next i
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by jolivanes.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to extract text string |
| How to alternate row colours based on text name |
| How to sum cell numerical values based on text suffix |
| How to convert decimals to text fraction |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.