VBA Code to delete information following a character

NYEXCEL1

Board Regular
Joined
Apr 17, 2013
Messages
123
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:

Cell Phone:Cell Phone - October:Verizon

<tbody>
</tbody>

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
try:
Code:
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
 
Upvote 0
I get a run time error '5'

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

any advice?
 
Upvote 0
Code:
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,703
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top