Compare two strings

[chrisppppp]

Excel 2007

Hi

I have a list of names that i've been given and i want to compare it to a list of names in our database. I've exported all of the names in our database out into Excel. The names from the database are in full strings, so not split into first, middle or last names ie:

Mr John Smith
Mr J Smith
Mr John Smith Esq

To compare the two strings i thought upon the lines of this formula:

=IF(ISNUMBER(FIND("v",A2)),"OK", "Not OK")

This is from the help by the way. However this is case sensitive. Is there any other way of comparing these strings that is case insentive, or a better way of doing this than above? Would it be better to make both sets of strings lower case using crystal or something to make the above formula work?

Thanks

 

[SkipVought]

Hi,

Find "v"?

Huh???

What you have is a database mess that will take a lot of blood sweat and tears. There is no good way of comparing these string and declaring victory.

What would be your criteria for comparison in your example?


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[chrisppppp]

That was just copy and pasted from the help in excel.

I was gonna take the last name of the list emailed to me, and then try and find it in the string of name in my database using excel. If there is a direct match then that is my criteria for comparison. I know it is isn't ideal, and multiple people can have the same surname but its the best i can do. all i've got to do is come up with a suitable list of suspects and then send them off, and let them worry about who they actually are.

So any ideas? Or any sort of fuzzy search techniques i could possibley play around with?

 

[SkipVought]

You have to have some specific rules.

1. Direct match. You could use the AutoFitlter to show all occurrences EQUAL TO something

2. Contains match. You could use the AutoFilter to show all occurrences where it CONTAINS something.

3. There is also BEGINS WITH and ENDS WITH matches.

4. You can use two criteria in the AutoFilter to narrow even more.

Using the AF might help you define what your rules ought to be. Fuzzy search is not an Excel feature beyond the features outlined above.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[chrisppppp]

Ok, thanks for you help, i'll take a look

Cheers

 

[AnotherHiggins]

-> Is there any other way of comparing these strings that is case insentive
Have a look at SEARCH. It works just like FIND but it isn't case-sensitive

->Would it be better to make both sets of strings lower case using crystal
FYI: LOWER and UPPER are Excel functions that will convert strings to all lower or all upper case, respectively

->or a better way of doing this than above?
My first thought was that you could try to break apart the names either using formulas or a macro. Chip Pearson has a great page on this here.

You'd have to adapt his formulas since your data isn't in the format "Last, First Middle".

Depending on the names you're dealing with, this might not be worth the effort because, without the comma delimiter, you couldn't tell the difference between two-part last names such as Van Wagner and someone with the first name "Van" and the last name "Wagner".

Still, if you wanted to go that route, your formulas would look something like this:

Honorific:
[tab][COLOR=blue white]=LEFT(A2, IF(ISERROR(FIND(" ", A2, 1)), LEN(A2), FIND(" ", A2, 1) - 1))[/color]

First:
[tab][COLOR=blue white]=TRIM(IF(ISERROR(FIND(" ", A2, 1)), A2, MID(A2, FIND(" ", A2, 1) + 1, IF(ISERROR(FIND(" ", A2, FIND(" ", A2, 1) + 1)), LEN(A2), FIND(" ", A2, FIND(" ", A2, 1) + 1)) - FIND(" ", A2, 1))))[/color]

Middle/Last:
[tab][COLOR=blue white]=TRIM(IF(ISERROR(FIND(" ", A2, FIND(" ", A2, 1) + 2)), A2, MID(A2, FIND(" ", A2, FIND(" ", A2, 1) + 1) + 1, IF(ISERROR(FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, 1) + 1) + 1)), LEN(A2), FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, 1) + 1) + 1) - FIND(" ", A2, FIND(" ", A2, 1) + 1)))))[/color]

Last/Suffix:
[tab][COLOR=blue white]=TRIM(RIGHT(A2, LEN(A2) - IF(ISERROR(FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, 1) + 2)) + 2)), LEN(A2), FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, FIND(" ", A2, 1) + 2) + 2)) - 1)))[/color]

[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.

 

[xlbo]

FWIW, to make FIND non case sensitive, you may use:

=IF(ISNUMBER(FIND("V",UPPER(A2))),"OK", "Not OK")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question