4 Replies Latest reply on Aug 3, 2017 5:59 AM by Ann-Marie Massenberg

# formula to match first 3 letters of text in two fields

I'm comparing two lists of names. Both lists have last name and first name. But List B has many people with the same last name as List A, but not the same first name.

Here is the result I'm looking for:

List A and List B Last Name     List A - First Name    List B - First Name   Result

Jones                                        Nancy                         Nancy C                     Nancy

Smith                                        Jon                              Jonathan                   Jon

Davis                                         Emily                           Emily                         Emily

Clarke                                        Jane                             Cathy

I've been playing with Filters, but I think that I will need a formula and am not sure what the formula should be. I'm thinking that it should compare List B First Name against List A  First name and return only those records where the first three letters of List B First Name exactly match the first three letters of List A First Name.

Cheers,

AM

• ###### 1. Re: formula to match first 3 letters of text in two fields

There are a lot of string functions you can use to do this.

Easiest would be

IF LEFT([String 1],3) = LEFT([String 2],3) then ... you have a match on the first 3 chars

But there is a MID() function you might want to use, or the CONTAINS() function.  Familiarize yourself with these functions.  Lots of cool stuff in that list.

1 of 1 people found this helpful
• ###### 2. Re: formula to match first 3 letters of text in two fields

You could do this:

IF CONTAINS([String 1], [String 2]) then [String 2]

ELSEIF CONTAINS({String 2], [String1]) then [String 1]

ELSE

"No match"

END

The code I suggested in the first reply will check only the first 3 chars and tell you if they match.  This calc will look both ways, and no matter how long the two strings are, if one is contained in the other it will tell you the one that is contained within the other (or it will result in "no match".)

So maybe this will work for you.  But it will also find "Marie" in "Ann-Marie", which may or may not suit your purposes.

You CAN force the looking to the front of the strings if needed.  We can discuss that if you have to do it that way.

2 of 2 people found this helpful
• ###### 3. Re: formula to match first 3 letters of text in two fields

Hi Joe,

Thank you! This worked and the link to String Functions was very helpful!

AM

• ###### 4. Re: formula to match first 3 letters of text in two fields

Hi Joe,

This is also very helpful. I will need to look at strings of varying lengths and will need to force it to look at only the first three letters of the string. Let me know if you have any recommendations for that.

Thanks,

AM