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.

 

[NYEXCEL1]

Ahh that makes sense. good to understand the steps in the process. That seems to work! thank you.

A few tweaks if you would:

- can you add language to insert a blank "column G" so the new data can go in and delete "Column F" after the process is done?
- For rows that do not have a ":" can you add language to bring the data in those cells to Column G?

thanks!!

 

[Paul Ked]

I'm away from my pc until the morning, I'll sort it out then.

Cheers

Paul Ked

 

[Paul Ked]

Thinking on...

The transferring of cells without a colon is no problem.

If you delete the original data and then run the sub again, it will clean out the existing data:

Original = "Cell Phone:Cell Phone - October:Verizon"
After 1st 'Clean' = "Cell Phone:Cell Phone - October" - What you asked for.
After subsequent 'Cleans' = "Cell Phone"

Is that what you want?

Best regards

Paul Ked

 

[NYEXCEL1]

I will only be running it once per report, so what you have put together is fine. If you can ask my earlier request of handling the adding and deleting columns when done and also if a cell is blank or has data that has no ":" then the original information must be brought into "column G"

thanks

 

[Paul Ked]

This will do what you want. But rather than the messy "Put data into column G and then delete column F" business, it just cleans the data in column F.

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
            Cells(x, 6) = Cells(x, 6)
            GoTo Skip
        End If
        Cells(x, 6) = Left(Cells(x, 6), InStrRev(Cells(x, 6), ":", -1) - 1)
Skip:
    Next
End Sub

Best regards

Paul Ked

 

[NYEXCEL1]

Thank you very much! Any tips on where to learn this type of coding?

 

[Paul Ked]

There are many out there, a 'Google' will show loads, but here is one my friend has just started and is making great progress with...

http://www.homeandlearn.org/