search for text with vlookup, then return that cell?

[seanh1016]

My 1 column list of 20,000 rows looks sort of like this for each contact:

A1=Name: Mr. Smith
A2=Address: 18 south st.
A3=Work Phone: 888-8888

The words, "Name:","Address:", and "Work Phone:" are contained in the same cell as the contents. I wish they could've been separated in the pasting process. But they weren't somehow.

In cell B1 I am trying to look for the first instance of "name:" then return "Name: Mr. Smith", or better yet, just "Mr. Smith." In cell B2, I am trying to return the second instance of "name:" occurrence. And so on.

I've tried vlookup=("name:",A1:A50,1,false) -- didn't work. ALso tried vlookupnth(which may be the best thing for me). That didn't work either. I have vlookupnth code. So, I am okay there.

Thanks

 

[Yogi Anand]

Hi seanh1016:

Is there a pattern to the list ... I mean a fixed number of rows for each name? I suggest you post a few rows of your data to display what you are working with.

If there is a well defined pattern to your data, a formula solution would work, otherwise you would be better off with a VBA solution.

 

[acw]

Hi

Assuming that there is a consistent 3 rows per each entry then

B1: =OFFSET($A$1,(ROW()-1)*3,0)
C1: =OFFSET($A$1,(ROW()-1)*3+1,0)
D1: =OFFSET($A$1,(ROW()-1)*3+2,0)

Copy down as required. This will organise the data. You can then copy and value paste the results, and remove the original data. You can then use the find / replace to remove the consistent items (Name: etc).

HTH

Tony

 

[seanh1016]

unfortuntely, no consistent # of rows for each name. Some have 2, some have 4.

 

[Yogi Anand]

unfortuntely, no consistent # of rows for each name. Some have 2, some have 4.

Then in that case a VBA solution would be the best choice. Try the following ...

Sub y_2()
    i = 1
    For Each cell In Range("a1", Range("a1").End(xlDown))
        If Left(cell, 5) = "Name:" Then
            Cells(i, 2) = Mid(cell, 6, 255)
            i = i + 1
        End If
    Next cell
End Sub

 

[seanh1016]

Please forgive me, I am retarded when it comes to VBA.

I am pretty sure all I do is:
-hit alt+F11
-insert module
-copy your code
-save and go back to excel
-then waht?

 

[Yogi Anand]

Then run the macro ... using

TOOLS|Macro|Macros

and select y_2 (the name I had given to my macro, you may give it a more meaningful name) and then click RUN

I hope this helps!

 

[seanh1016]

I ran the macro and nothing happened. Should I be looking in a specific cell for results?

 

[babycody]

Couldn't you use Edit>Replace to remove Name: and the other unwanted text?

 

[Yogi Anand]

I ran the macro and nothing happened. Should I be looking in a specific cell for results?

Hi seanh1016:

Let us say following is my worksheet with data ...

y051120h1.xls
	A	B	C	D
1	Name:A_1
2	address:
3	w_phone:
4	Name:A_2
5	address:
6	w_phone:
7	Name:A_3
8	address:
9	w_phone:
10	h_phone:
11	Name:A_4
12	address:
13	w_phone:
14	Name:A_5
15	address:
16	w_phone:
17	Name:A_6
18	address:
19	w_phone:
20	Name:A_7
Sheet8

then on running the following macro ...

Sub y_2()
    i = 1
    For Each cell In Range("a1", Range("a1").End(xlDown))
        If Left(cell, 5) = "Name:" Then
            Cells(i, 2) = Mid(cell, 6, 255)
            i = i + 1
        End If
    Next cell
End Sub

here is the resulting worksheet ...

y051120h1.xls
	A	B	C	D
1	Name:A_1	A_1
2	address:	A_2
3	w_phone:	A_3
4	Name:A_2	A_4
5	address:	A_5
6	w_phone:	A_6
7	Name:A_3	A_7
8	address:
9	w_phone:
10	h_phone:
11	Name:A_4
12	address:
13	w_phone:
14	Name:A_5
15	address:
16	w_phone:
17	Name:A_6
18	address:
19	w_phone:
20	Name:A_7
Sheet8

Isn't this what you are looking for?