Compare Text

Miles Allison

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

Paul Newcome

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.

Paul Newcome

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?

Miles Allison

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

Paul Newcome

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.

Miles Allison

That should do it. Thanks!

Paul Newcome

Happy to help. 👍️