Basic macro using a named range?

[petemania]

Hello,

I have a spreadsheet containing details of a few thousand newspaper articles.

At present, column A contains both the headline and newspaper name, as below:

A1: Headline1 The Toronto Star
A2: Headline2 The Toronto Sun
A3: Headline3 Windsor Star (Ontario)
A4: Headline4 Winnipeg Sun (Manitoba, Canada)

However, I would like to have the headlines and newspaper titles separated into the different columns (e.g. 'Headline1' in A1 and 'The Toronto Star' in B1, etc)

If I were to create a list containing all the newspaper titles (named, say 'NewspaperTitles'), would it be possible to have a macro that goes through column A and uses Replace to insert a delimiter in front of the newspaper title?

So, for example, using the four articles above, could I have a list containing the newspaper titles – 'The Toronto Star', 'The Toronto Sun', 'Windsor Star (Ontario)', 'Winnipeg Sun (Manitoba, Canada)' – and a macro that goes through column A changes every instance of 'The Toronto Star' to '*The Toronto Star', every instance of 'The Toronto Sun' to '*The Toronto Sun', etc?

I hope that makes sense.

Any help would be very greatly appreciated.

Cheers,
Pete

 

[Ruddles]

You say you want the headlines separated from the titles and then you ask for a routine to insert asterisks. Is that because you intend to use the asterisks to split the headlines from the titles?

This routine will, assuming your headline/title data is in column A of Sheet1 and a list of titles is in column A of Sheet2, split the data in Sheet1 into separate columns:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ExtractTitles()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim wsH As Worksheet
  Dim wsT As Worksheet
  
  Dim iHeadline As Long
  Dim iHeadlines As Long
  Dim iTitle As Long
  Dim iTitles As Long
  
  Dim iLength As Integer
  
  Set wsH = ThisWorkbook.Sheets("Sheet1")
  Set wsT = ThisWorkbook.Sheets("Sheet2")
  
  iHeadlines = wsH.Cells(wsH.Rows.Count, 1).End(xlUp).Row
  iTitles = wsT.Cells(wsT.Rows.Count, 1).End(xlUp).Row
      
  For iTitle = 1 To iTitles
    iLength = Len(wsT.Cells(iTitle, 1))
    For iHeadline = 1 To iHeadlines
      If Right(wsH.Cells(iHeadline, 1), iLength) = wsT.Cells(iTitle, 1) Then
        wsH.Cells(iHeadline, 2) = wsT.Cells(iTitle, 1)
        wsH.Cells(iHeadline, 1) = Left(wsH.Cells(iHeadline, 1), Len(wsH.Cells(iHeadline, 1)) - iLength - 1)
      End If
    Next iHeadline
  Next iTitle
  
End Sub[/FONT]

Always test on a copy of your valuable data!

 

[petemania]

Perfect. Thank you very much.

(Apologies for the confusing description. The asterisk part was because I'm a novice and thought this was a two part process: 1) insert an asterisk, 2) use Text to Columns to separate them, using the asterisk as the delimiter. Your solution has blown my mind.)

 

[Ruddles]

I'm hoping that means you tried it and it works. If so, I'm pleased.

If you see a group of bikes go past you on the 29th and one of them looks like my avatar, flag me down and buy me a coffee!