Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Compare Text
Is there a formula that I can use to compare the amount of similar words between two cells? I have two columns that have similar text but sometimes there is a period or comma that throws of my MATCH formula.
Thanks,
Miles
Best Answer
-
Not directly and the complexity of the solution is going to depend on how many different characters you anticipate. Basically we could use some nested SUBSTITUTE functions to strip out those special characters and spaces and compare the results from that.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([1st Text String]@row, "-", ""), " ", ""), ".", "")
The above removes hyphens, spaces, and periods.
Answers
-
Are you wanting to use this within an existing formula? Are you wanting some kind of alert or flag if something doesn't match? Are you wanting to strip all "special characters" and just leave the text for comparison? How exactly are you wanting to use this?
-
I'm looking for something that can indicate a partial match. For example, one cell contains "Case Closed - Insufficient Information" and the other cell contains "Case Closed. Insufficient Information" is there a way to identify that as a match?
Thanks
-
Not directly and the complexity of the solution is going to depend on how many different characters you anticipate. Basically we could use some nested SUBSTITUTE functions to strip out those special characters and spaces and compare the results from that.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([1st Text String]@row, "-", ""), " ", ""), ".", "")
The above removes hyphens, spaces, and periods.
-
That should do it. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 152 Industry Talk
- 499 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have a test scenario as a parent and a sub-task as a child. So let's say, I have 11 sub tasks under the parent, if one of those 11 children has fail in its status, the parent will say fail. If all s…
- I am trying to create a formula to count the number of orders that are on hold. However, we have several hold types, so I just need to count any order that says 'hold' in the field. Here is my current…
- I know this should be so simple and I have tried looking at the Help and Community channel but still getting hung up. All I want to do is based my % Complete on 4 checkboxes, if the checkbox is checke…