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
Check out the Formula Handbook template!