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!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!