PDA

View Full Version : Solved: Trim titles



CCkfm2000
05-11-2010, 08:59 AM
Hi,
Let me thank you before I start. Thankyou :beerchug:

On a worksheet I have a list of course titles, see below. what I want is some vba code that will trim the end part. The list starts in cell B2, this is only a sample of the list it can be as much as 1000 titles.

As you can see the title length differs from one to the next. (I hope this makes sense).

FROM THIS
Appraisal Review Interview Workshop 13-Aug-2010 REC
Appraisal Review Interview Workshop 16-Jul-2010 SEC
Appraisal Review Interview Workshop 22-Sep-2010 SEC
Appraisal Review Process Workshop 03-Aug-2010 REC
Appraisal Review Process Workshop 05-Jul-2010 SEC
Appraisal Review Process Workshop 09-Sep-2010 SEC
Basic Adult Resuscitation Medical 07-Sep-2010 REC 08:50
Basic Adult Resuscitation Medical 07-Sep-2010 REC 09:45
Basic Adult Resuscitation Medical 22-Oct-2010 SEC 08:50
Basic Adult Resuscitation Medical 23-Jul-2010 REC 12:50
Basic Adult Resuscitation Medical 23-Jul-2010 REC 13:45
Basic Adult Resuscitation Medical 24-Aug-2010 SEC 08:50
Basic Adult Resuscitation Medical 24-Aug-2010 SEC 09:45
Basic Adult Resuscitation Medical 10-Dec-2010 SEC 12:50
Basic Adult Resuscitation Medical 10-Dec-2010 SEC 13:45
Basic Adult Resuscitation Medical 19-Nov-2010 REC 12:50
Basic Adult Resuscitation Medical 19-Nov-2010 REC 13:45
Basic Adult Resuscitation Medical 22-Oct-2010 SEC 09:45
Basic Adult Resuscitation Non Clinical 22-Oct-2010 REC 12:45
Basic Adult Resuscitation Non Clinical 23-Jul-2010 REC 12:45
Basic Adult Resuscitation Non-Registered 01-Nov-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 01-Nov-2010 REC 10:40
Basic Adult Resuscitation Non-Registered 04-Oct-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 04-Oct-2010 REC 10:40
Basic Adult Resuscitation Non-Registered 05-Jul-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 05-Jul-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 06-Dec-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 06-Dec-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 06-Sep-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 06-Sep-2010 REC 10:40
Basic Adult Resuscitation Non-Registered 08-Nov-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 08-Nov-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 09-Aug-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 09-Aug-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 11-Oct-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 11-Oct-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 12-Jul-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 12-Jul-2010 REC 10:40
Basic Adult Resuscitation Non-Registered 13-Dec-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 13-Dec-2010 REC 10:40
Basic Adult Resuscitation Non-Registered 13-Sep-2010 SEC 08:50
Basic Adult Resuscitation Non-Registered 13-Sep-2010 SEC 10:40
Basic Adult Resuscitation Non-Registered 15-Nov-2010 REC 08:50
Basic Adult Resuscitation Non-Registered 15-Nov-2010 REC 10:40
Conflict Resolution 21-Sep-2010 REC 09:00 V&A

TO THIS
Appraisal Review Interview Workshop
Appraisal Review Interview Workshop
Appraisal Review Interview Workshop
Appraisal Review Process Workshop
Appraisal Review Process Workshop
Appraisal Review Process Workshop
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Medical
Basic Adult Resuscitation Non Clinical
Basic Adult Resuscitation Non Clinical
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Basic Adult Resuscitation Non-Registered
Conflict Resolution

Thanks for your help

mdmackillop
05-11-2010, 09:23 AM
This should delete from the first number

Option Explicit
Sub test()
Dim Matches
Dim cel As Range
Dim RegExp As Object
Set RegExp = CreateObject("VBScript.RegExp")
With RegExp
.Global = True
.IgnoreCase = True
End With
RegExp.Pattern = "\d"
On Error Resume Next
For Each cel In Selection
Set Matches = RegExp.Execute(cel)
If Err = 0 Then cel = Trim(Left(cel, Matches(0).firstindex))
Err.Clear
Next
End Sub

CCkfm2000
05-12-2010, 08:04 AM
:cloud9: that looks good thanks. :cloud9: