Excel help - highlight or return differing characters in compared cells

Need some quick Excel help from the gurus, please.

We're doing data hygiene on our autocross preregistrations and it is tedious.

Does anybody have a formula that returns the character number that is different when comparing strings in 2 different cells?  Or highlights that character?  Or something that indicates where the difference is?

It's easy enough to compare the 2 strings and have it return whether there is or is not a difference.  We're looking for something a little more nuanced that will point out where the difference is.

Thanks in advance!

can you give me an example of two cell values?  I think you need to resort to VBA to compare on a character by character basis (unless you know which character you want to compare to begin with - last, first, or a certain #)

The 2 cell values will be concatenated from the columns for name, car, number, class, etc.  Comma separated (or otherwise, since we're doing the concatenating).  Then we're trying to compare the info from our master list to the preregistration download from MSR.  We can easily highlight a different entry but we'd like to point out where the divergence is.

Thanks.

Also, something like Vlookup but that returns near matches would be helpful.  Thanks again.

I'm not sure if conditional formatting is powerful enough for you needs, but it is pretty easy.

https://support.office.com/en-us/article/Apply-conditional-formatting-in-Excel-34402F91-C7E7-4060-944C-65D913033D18

Basically what I'm looking for is to search our master list to find the closest match to the MSR string - even if it is not exact, pull it, and then highlight where the difference is.

Because of the varying length and format we can't effectively use a wildcard VLOOKUP.  But once we pull the closest-match data from the master list, we have a formula that will compare them and tell us where the first diverging character is.

Hmm, I think I would still need to see something.

If you are looking for near matches you can use a wildcard search with Vlookup if that helps (for example only search by last name, car, etc.).  But if you are trying to catch spelling mistakes that would still be problematic.

Example:  =vlookup(*txt*,[range],[index],[sorted])

Edit: doh too slow

This may do what you need:

Fuzzy Lookup Add-In for Excel

https://www.microsoft.com/en-us/download/details.aspx?id=15011

It would be useful to see a couple of pair of concatenated strings to see what you are dealing with. 

Is there a reason that you don't compare the information in the raw data prior to the concatenated cells?  Might be easier...

@ProDarwin:  Thanks! I need to investigate the Fuzzy Lookup.  The problem with Vlookup is that if you search for a partial or wildcard match it only returns the first hit, not the best.

@CJ:  We're trying to make each value as unique as possible so we can run 1 check.

You can always split the strings into one column per letter and then compare that way.

Did you ever come up with something good?

Well, we got something that works, anyway.  We ended up just concatenating enough of the info to make each unique, then using Vlookup to retrieve all the rest of the information and compare it cell by cell between the two sources, with some error trapping and conditional formatting to highlight the differences.  That gets us so that the retrieval rate is over 90% and we only have a few failures (not enough unique info) to check manually.

It's a bit kludgy and there may be a more elegant solution, but for now this seems to work.  Thanks for your help and follow up!

Beyond Compare is a free download that will do what you want. Use it all the time at work.