Extracting specific information from data

[Steve_123]

Hi there forum.

I'd like to be able to pull out specific information from some data and organise it into columns. The columns are Name, Title, Company, Field of Study, University / College, Year of Graduation, Lives In, State / Country

The dataset to extract from will always only be in Column A, but could be an infinite number of rows.

I've tried to do it using simple formulas but am coming unstuck as there isn't a strict pattern to the number of rows between data (hope that makes sense). For example, Name information may be on row 1, 10, 15, 28, 34 etc.

In terms of rules for where the data sits:

Name is always present and is always one row below More Options, except for the first row of the data, which will also always be Name

Title is present in the row below Name, provided the word 'at' is present in the row below Name. If 'at' is present in the row below Name, Title is all the words in that cell that precede but don't include 'at'. If 'at' isn't present, this row is to be ignored and Title left blank.
Company is also present in the row below Name, provided the word 'at is present. If 'at' is present in the row below Name, Company is all the words in that cell that follow but don't include 'at'
Eg. 'Engineer at BPC' would mean Title is Engineer and Company is BPC

Field of Study is always the row that begins with 'Studied' and is always the words that follow but don't include 'Studied' and precede but don't include 'at'
University / College is always the row that begins with 'Studied' and is always the words that follow but don't include 'at', and precede but don't include 'apostrophe number' - eg. '11
Year of Graduation is always the number that is at the end of the row that begins with 'Studied' and is preceded by an apostrophe eg. '11.
eg. Studied Maths at*University of Leeds*'11 would mean Field of Study is Maths, University / College is University of Leeds and Year of Graduation is '11

Lives In is always the row that begins 'Lives In' and is always the words that follow but don't include 'Lives In' and precede but don't include ',' (comma)
State / Country is always the row that begins 'Lives In' and is always the words that follow but don't include ',' (comma)
eg. Lives in*Leicester, United Kingdom would mean Lives In is Leicester and State / Country is United Kingdom.

In some cases, the data required for extraction may not be present. If that is the case, please can the fields be left blank, as per sample data attached.

The sample data has two tabs attached - Raw Data and Extracted Data.

Any data not catered for above should be ignored.

Thanks very much in advance for any help.

Steve

[Olly]

Try:

Please Login or Register  to view this content.

[Steve_123]

Hi Olly

Thanks very much for this. I have applied it and it's working to an extent, but falls over after the third record. It presents the first three correctly, then states

Subscript out of range

I've realised my instructions weren't quite clear enough - apologies for the confusion, I did try and pick up all information but missed some. Sorry Olly!

I should have stated....

Sometimes there will not be cells that begin Studied or Lives In for a particular record.

Each record begins with the Name and finishes with the next More Options.

For example:

Chris Wade
Director at BPC
Addition
Message
More Options

In this record, there is no Lives in or Studied and the record is brief.

Once again, sorry for the poor instructions, thought I had everything covered!

Thanks for your patience and continued help.

Steve