Extract cell after certain text

Gwill1983

New Member
Joined
Oct 24, 2018
Messages
49
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 24, 2018
Messages
49
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Is "Contact Venue" on it's own in a cell, or is there other text with it?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,967
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top