Extract cell after certain text

[Gwill1983]

Hi all,

I have been supplied with a list of venue addresses, but it has been extracted from its source into excel all within column A!

I need to extract certain information into other columns (name, postcode) but ideally don't want to trawl through thousands of entries and remove the lines that I don't need.

Is there anyway of returning cells depending on the text in the cell above?

Any ideas at all would be much appreciated.

Chris

 

[tyija1995]

Hi Chris,

Is the data in any sort of consistent pattern or entirely random? Can you send some lines so that we can see?

Thanks
Matt

 

[Gwill1983]

Not consistent unfortunately! Each entry is across either 17 or 18 rows dependant on the length of the venue address.

For example for the first entry:

Row 1 is the venue name
2 -4 is the address. This spreads to row 5 if it is a long address knocking all of entries down by 1 row.
5 is the postcode
6 is phone number
7 is email
8-17 are various notes about the venue which aren't important to me.
Each field ends with the same wording "Contact Venue" which is then immediately followed by the next venue name. Not sure if there is anything that can be done to lookup cells after the cell containing "Contact Venue"


I suspect that I will need to extract the data manually, but thought it was worth a shot of asking if anybody could save the day!

 

[Fluff]

Is "Contact Venue" on it's own in a cell, or is there other text with it?

 

[Gwill1983]

On its own and in its own cell.

Chris

 

[Fluff]

How about

Sub Gwill1983()
   Dim Rng As Range
   Dim Ofst As Long
   
   With Range("A1", Range("A" & Rows.count).End(xlUp))
      .Replace "Contact Venue", "", xlWhole, , False, , False, False
      For Each Rng In .SpecialCells(xlConstants).Areas
         If InStr(1, Rng.Offset(6).Resize(1, 1), "@") > 1 Then Ofst = 7 Else Ofst = 8
         Rng.Offset(, 1).Resize(1, Ofst).Value = Application.Transpose(Rng.Resize(Ofst).Value)
      Next Rng
   End With
End Sub