a month ago
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.
Solved! Go to Solution.
a month ago
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,
4 weeks ago - last edited 4 weeks ago
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-']", "" )
a month ago
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:
Once I understand how you'd like to deal with these situations we might be able to get a formula written for you.
Regards,
a month ago
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.
a month ago
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,
a month ago
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,
a month ago
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.
a month ago
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.
4 weeks ago
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,
4 weeks ago - last edited 4 weeks ago
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-']", "" )
4 weeks ago
That's really helpful info in gaining perspective. Thanks Anthony.
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog