VBA Code to delete information following a character

[NYEXCEL1]

I have a report where every cell in column "F" has an expense description separated by multiple colon ":" symbols. I cannot seem to write a macro that will find the last colon in the cell and eliminate everything following it, including the last colon as well.

I would like the program to do this for every cell in the entire column as I dont know how long the report will be and there are blanks in between cells at times.

Here is the format in the cells:

[TABLE="width: 808"]
<tbody>[TR]
[TD="width: 808"]Cell Phone:Cell Phone - October:Verizon[/TD]
[/TR]
</tbody>[/TABLE]

thank you so much and let me know if you have questions.

 

[Paul Ked]

try:

Sub Clean()
    Dim lr As Long, x As Long
    lr = Cells(Rows.Count, 6).End(xlUp).Row
    For x = 1 To lr
        If Cells(x, 6) = "" Then GoTo Skip
        Cells(x, 7) = Left(Cells(x, 6), InStrRev(Cells(x, 6), ":", -1) - 1)
Skip:
    Next
End Sub

Best regards

Paul Ked

 

[NYEXCEL1]

I get a run time error '5'

Invalid procedure call or argument in the last line Cells(x, 7)....

any advice?

 

[Paul Ked]

What version of Excel are you using?

 

[NYEXCEL1]

2013

 

[Paul Ked]

It works fine for me.

https://www.dropbox.com/s/29jn9ia0gdbv2k6/Get left of last colon.xlsm?dl=0

That should be a link to the file.

 

[Paul Ked]

The error is created when one of the items in the list does not contain a ":".

 

[Paul Ked]

Sub Clean()
    Dim lr As Long, x As Long
    lr = Cells(Rows.Count, 6).End(xlUp).Row
    For x = 1 To lr
        If Cells(x, 6) = "" Then GoTo Skip
        If InStr(1, Cells(x, 6), ":") = 0 Then GoTo Skip
        Cells(x, 7) = Left(Cells(x, 6), InStrRev(Cells(x, 6), ":", -1) - 1)
Skip:
    Next
End Sub

Should work!

 

[NYEXCEL1]

thanks.

I am not getting any errors but when i run nothing happens. does the sheet name need to be selected or something? the cell selected is A1 and when i run, A1 is still selected.

 

[Paul Ked]

The list should be in column F "Cells(x, 6)", as per your original post, and the 'corrected' data in column G "Cells(x, 7)" after the sub has been run.

The correct sheet must be active as I did not put any sheet reference (eg Sheet1.Cells(x, 6) or Sheets("Data").Cells(x, 6)) in the sub.

You don't need to select anything.