# search for text with vlookup, then return that cell?

#### seanh1016

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

A1=Name: Mr. Smith
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

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

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

seanh1016 said:
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 ...
Code:
``````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``````

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

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

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!

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

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

seanh1016 said:
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
ABCD
1Name:A_1
3w_phone:
4Name:A_2
6w_phone:
7Name:A_3
9w_phone:
10h_phone:
11Name:A_4
13w_phone:
14Name:A_5
16w_phone:
17Name:A_6
19w_phone:
20Name:A_7
Sheet8

then on running the following macro ...
Code:
``````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
ABCD
1Name:A_1A_1
3w_phone:A_3
4Name:A_2A_4
6w_phone:A_6
7Name:A_3A_7
9w_phone:
10h_phone:
11Name:A_4
13w_phone:
14Name:A_5
16w_phone:
17Name:A_6
19w_phone:
20Name:A_7
Sheet8

Isn't this what you are looking for?

Replies
7
Views
216
Replies
10
Views
530
Replies
0
Views
273
Replies
6
Views
527
Replies
1
Views
175

1,203,379
Messages
6,055,095
Members
444,761
Latest member
lneuberger

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back