1st, 2nd, 3rd occurence of text search from the right

[seanh1016]

Here's an example of my cells:
a1=person one Name:
a2=person one address:

I'm looking for every occurence of "Name:" It is always on the rightmost side of the cell. And each occurrence is not an evenly spaced amount of rows in between.

Once I find each occurence, I'd like to stack the names in a column.

 

[cfree36]

Here is what I do... might be an easier way.... but this works.

Add two columns to the left of A (you will hide these later)
Now your A data should be in C

In column B enter the following formula:
=if(right(A1,5)="Name:",1,0)
Copy the formula down column B

In column A enter the following formula:
=if(B1=0,0,sum(B1:$B$1))
Copy the formula down column A

Column A counts each occurance of 1 in column B
Column B finds each occurance of "Name:" and marks it with a 1

Now you can name your range A1:Whatever

Do a lookup on another sheet to reference the names.

Column A enter 1-whatever

Lookup:

=vlookup(A1,range,column index,0)