How to find texts in a column???

[arvarr]

I am having this problem. I can't seem to find the first name and the last name using two conditions to search in another range located in sheet 2.

Because the data in sheet 2 does not split the names into first and last name and also comes with the prefix like Mr. or includes Ms. in the middle of the name, I am not sure what I can do.

Sheet 1
Column A = First Name
Column B = Last Name

E.g.

A1 = Paul
A2 = David
A3 = Cameron
A4 = Michelle

B1 = Fowler
B2 = Maz
B3 = Lassi
B4 = Bird

Sheet 2
A1 = Mr. Paul Fowler
A2 = Cameron Lassi Mr
A3 = Mr Cameron, Mike
A4 = Bird, Ms. Michelle

Can anyone help out?
Thanks.

 

[SkipVought]

Hi,

You have a classic data cleanup problem. It will be very messy. Here are the apparent problems that need to be solved...
[tt]
1. Your sheet2 data does not have a consistent format
2. There may be cases where first or last names contain one or more spaces.
[/tt]
The process involves classifying the data into similar groupes and then applying a set of rule(s) to that group. The end result is to isolate the first and last names.

In your case, primarily because of item 1, it will most likely be a long, painfully arduous task.

Here are some tips.

1. copy the sheet2 data to a working column in order to preserve the original.

2. use Edit > Replace... to remove the prefixes.

3. the COMMA indicates LAST FIRST, otherwise FIRST LAST.

4. use the FIND function to identify cells with COMMA. this is a categiory. What's left is another category.

5. use Data > text to columns... to parse each category

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Thanks for your reply.

Yes, this is a very messy thing indeed.
I have removed all the commas and tried a few methods and it doesnt work.

Is there a formula to look for First Name and also Last Name against Sheet 2 without having to manipulate data in sheet 2?

I know if using the Find function against one cell in the same Sheet, the Find formula works fine but not in another sheet.

E.g
Sheet1 Cell A1 = Ms. Nancy Lee
If using formula = Find("Lee",A1), formula works well.

But if i need to find "Lee" against range data in Sheet 2, it comes up with #Value

Is there a problem with my formula?

Therefore, I have two questions:-

1. Is there a formula to look for First Name and also Last Name against Sheet 2 without having to manipulate data in sheet 2?

2. Why cant I find "Lee" against range data in Sheet 2?

Hope I'm not too confusing.
Thanks.

 

[SkipVought]

[I have removed all the commas and tried a few methods and it doesnt work.
[/quote]
I never suggested removing COMMAS. They indicate something VERY IMPORTANT!

FIND is not a LOOKUP. A LOOKUP looks for values in a range of cells. FIND looks for a string within a string.

Is there a problem with my formula?

What formula?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Dear All
How do i copy and past data from txt file into one column in Excel.

Day 1 - Copy and Paste Data from txt file into Excel, works well, everything falls into one column.

Day 2 - Copy and Paste Data from txt file into Excel, but it seems that data is populated into multiple columns.

No setting have changed, the data is exactly the same.

What went wrong? Can anyone kindly advise?

Thanks.

 

[arvarr]

Dear All
How do i copy and past data from txt file into one column in Excel.

Day 1 - Copy and Paste Data from txt file into Excel, works well, everything falls into one column.

Day 2 - Copy and Paste Data from txt file into Excel, but it seems that data is populated into multiple columns.

No setting have changed, the data is exactly the same.

What went wrong? Can anyone kindly advise?

Thanks.

 

[SkipVought]

arvarr,

Very annoying and not good Tek-Tips or web etiquette to leave a thread dangling without closure.

You have 1) not indicated whether your original question reached conclusion and 2) posted a new question in an existing thread.

I did notice, however, that you posted this new question in a new thread.

Please post yea or nea regarding the primary issue in this thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Sorry Skip,
I posted to the wrong area and thus repost in a new thread.
My apologies.

Well, the above method did not work so I just had to leave it and work through it manually.

Thanks for the advice. Not sure how to close a thread.
If you can let me know, I'll close this one. Thanks.

 

[AnotherHiggins]

arvarr - we don't manually close threads here at Tek-Tips*. But it's considered proper etiquette to post back and let us know if we've helped you or not. If an offered solution hasn't worked, we can probably help you work through any issues you're having if you provide details about what you've tried and where it's failed to do what you expected.

*Note: threads are closed to new comments automatically after a lengthy period of inactivity.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[arvarr]

Thanks. Will do so next time.