Macro to edit string in excel at a certain word

03-18-2011, 05:53 AM

bobbyna

Newcomer

Join Date: Mar 2011

Posts: 2

Macro to edit string in excel at a certain word

Hi all

It is my first time on this forum, so have done a couple of searches before to get some information to help.

However I can not seem to find a thread to help me with this particular problem.

I will keep it simple:

I have a number of cells all in one column i.e. A1

They contain a string so for example

A1 : DERBY R.T.C note: TRANSIT DERBY RTC - SILKSTREAM JN

A2 : BRENT CURVE note: SGT RECORD BRENT CURVE JN - ACTON WEST

A3 : ACTON WEST note: SGT RECORD ACTON WEST - ACTON WELLS JN

There can be any number of these “notes” from 0 – 50

I know enough VB to be able to loop through these cells until I reach the end however I can not figure out how I can cut out all the text after ‘note:’

There is a lot more to the sheet in that I am taking bits of information out and pasting them to another spread sheet called ‘Sheet1’ in the equivalent cells

Therefore I would need to be able to take for example :- ‘TRANSIT DERBY RTC - SILKSTREAM JN’ and put it into Sheet1 in the same cell A1

Thanks in Advance

Bobby

03-18-2011, 02:45 PM

JSTKwan

Centurion

Join Date: Mar 2007

Posts: 115

try this

Code:

Sub ParseData()
    Dim LastRow As Long
    Dim lRow As Long
    Dim sData As Variant
    
    LastRow = FindLastRow(Worksheets("sheet1"), "A")
    
    For lRow = 1 To LastRow
        sData = Split(Cells(lRow, "A"), "note:")
        MsgBox sData(0)
        MsgBox sData(1)
    Next lRow
End Sub
Public Function FindLastRow(WS As Worksheet, ColumnLetter As String)
    FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function

I am echoing the result out, you can do whatever with it