Modifying contact names with special characters

[PeteIntradiem]

I have first name and last name, with special characters, and can't figure out a formula. In the majority of cases I want to keep the format as is in SFDC (often spaces).  Assume (first name) Mary Ann and (last name) Williams Smith.  The resulting NEW names in DemandTools become First = Mary Ann Williamsn, Last = Smith.  The names are correct in SFDC but there are thousands of Contact records.

Any ideas on how I would account for special characters, keeping some, namely spaces.  And also removing some, like =  , -, ., (, ),? I'm using the ParseValueNameReturn operator, but have no idea how to write the formula and/or conditions.

[AnthonyValidity · Accepted solution]

@PeteIntradiem ,

While I wait, I did want to provide a possible solution that would probably reduce the amount of manual labor you would then have to do in the Tune module.  The Tune module allows you to view and edit fields real time and I think you'd have to review the three and great than four strings.

Formula to apply to full name to the First Name field.  You probably have the string in a different field name but just as an example:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, LEFT(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) - 1), LEFT(${Name}, FIND(" ", ${Name} & " ") - 1) ) ), "[^a-zA-Z0-9\s-']", "" )

Formula to apply to full name to the Last Name field.  You probably have the string in a different field name but just as an example:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, MID(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) + 1, LEN(${Name})), ParseNameReturnLastName(${Name}) ) ), "[^a-zA-Z0-9\s-']", "" )

Both formulas will strip the special characters from the string after counting and applying formulas.  It will count words that are seperated by spaces or hyphens.  

First formula looks to see if there are four words and will grab just the first two for the first name field.  Otherwise, it will grab the first word in the string for the first name field.

Second formula looks to see if there are four words and will just grab the last two for the last name field.  Otherwise, it will grab the last word in the string for the last name field.

After all this is done, you'd have to sort through the full names that have three or more words, using the following Record Selection Criteria in Tune and then pulling in the full name string (whatever that is called in your system), First Name, and Last Name.  You will look through First Name and Last name and hopefully be able to edit and fix any that don't fit into the patterns we used in the formulas. 
Name "like" % % %

If you think you need help with this, we can get a case started for you.

Regards,

[AnthonyValidity]

Hi @PeteIntradiem ,

From what I'm reading, you have a single field with a full name in it and you'd like to parse out the full name into corresponding first name and last name fields.  Is that correct?

We can predict if there are four words, that the first two is the first name and the last two are the last name, but what would you like to happen if:

1. Somehow the middle name is included in the string or
2. There are three words and we're not sure if the second word belongs in the first name field or in the last name field?

Once I understand how you'd like to deal with these situations we might be able to get a formula written for you.

Regards,

[PeteIntradiem]

Hi Anthony,

Yes, you've described it perfectly.  In fact, even in cases of more than four words, e.g. Mary Jo van der Maten, Maten is the last name.  Those cases are fewer. 

[AnthonyValidity]

Thanks, @PeteIntradiem .  How would you like to handle three words in the string?  How about if there's more than 4?  This is very hard to predict of course.  

Thanks,

[AnthonyValidity · Accepted solution]

@PeteIntradiem ,

While I wait, I did want to provide a possible solution that would probably reduce the amount of manual labor you would then have to do in the Tune module.  The Tune module allows you to view and edit fields real time and I think you'd have to review the three and great than four strings.

Formula to apply to full name to the First Name field.  You probably have the string in a different field name but just as an example:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, LEFT(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) - 1), LEFT(${Name}, FIND(" ", ${Name} & " ") - 1) ) ), "[^a-zA-Z0-9\s-']", "" )

Formula to apply to full name to the Last Name field.  You probably have the string in a different field name but just as an example:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, MID(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) + 1, LEN(${Name})), ParseNameReturnLastName(${Name}) ) ), "[^a-zA-Z0-9\s-']", "" )

Both formulas will strip the special characters from the string after counting and applying formulas.  It will count words that are seperated by spaces or hyphens.  

First formula looks to see if there are four words and will grab just the first two for the first name field.  Otherwise, it will grab the first word in the string for the first name field.

Second formula looks to see if there are four words and will just grab the last two for the last name field.  Otherwise, it will grab the last word in the string for the last name field.

After all this is done, you'd have to sort through the full names that have three or more words, using the following Record Selection Criteria in Tune and then pulling in the full name string (whatever that is called in your system), First Name, and Last Name.  You will look through First Name and Last name and hopefully be able to edit and fix any that don't fit into the patterns we used in the formulas. 
Name "like" % % %

If you think you need help with this, we can get a case started for you.

Regards,

[PeteIntradiem]

Yes, that's the complexity.  Looking at the data patterns, I would like to accomplish conditions and formulas for If 3 words: First-Last-Last....If 4 words: First-First-Last-Last, If 5 or more First-First-Last-Last-Last.  I would view modified, export grid, fix in Excel, Modify Update.  Would you mind helping me to set this up please?  I would be grateful.

[PeteIntradiem]

Anthony, first I appreciate this work very much.  My second reaction is I referred to myself as not versed in formulas and how comical that is.  Your formulas encourage me that in a year, I may be able to write it. Wow!  Thank you very much.

[AnthonyValidity]

It is my pleasure!  I'm sure you're going to learn more really fast using Modify and real life examples.  Don't forget, any formula name that is in all caps means it is a spreasheet formula that you can google how to use.  The propercased ones are ours and you can find most of them here:  https://knowledge.validity.com/s/articles/DemandTools-Modify-Formula-Dictionary?language=en_US

Regards,

[AnthonyValidity · Accepted solution]

I can't find my last post where I corrected it to do what you wanted as far as 2, 3, 4, and 5 or more.

Looking at the data patterns, I would like to accomplish conditions and formulas for If 3 words: First-Last-Last....If 4 words: First-First-Last-Last, If 5 or more First-First-Last-Last-Last.

---

First name:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, LEFT(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) - 1), IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 3, LEFT(${Name}, FIND(" ", ${Name} & " ") - 1), IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) >= 5, LEFT(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) - 1), "" ) ) ) ), "[^a-zA-Z0-9\s-']", "" )

Last Name:

REGEXREPLACE( IF(${Name}="", "", IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) = 4, MID(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) + 1, LEN(${Name})), IF((LEN(TRIM(SUBSTITUTE(SUBSTITUTE(${Name}, "-", " "), " ", " "))) - LEN(SUBSTITUTE(SUBSTITUTE(TRIM(${Name}), " ", ""), "-", "")) + 1) >= 5, MID(${Name}, FIND(" ", ${Name} & " ", FIND(" ", ${Name} & " ") + 1) + 1, LEN(${Name})), ParseNameReturnLastName(${Name}) ) ) ), "[^a-zA-Z0-9\s-']", "" )

[PeteIntradiem]

That's really helpful info in gaining perspective.  Thanks Anthony.