The problem - I want to decide how similar two strings are based on the order in which the letters appear.
For instance, comparing the strings "Paul" and "JoPaul". JoPau has 2 extra characters and one missing character compared to Paul, and the characters which match are not in the same position (e.g. P is the first character in Paul, but the third in JoPau, a is the 2nd character and the 4th character and so on.
However, JoPau is more similar to Paul than the string JPuao. In JoPau, a follows P, and u follows a, the same as it does in Paul. However, none of the characters is followed by the 'correct' character in JPuao.
My current attempt:
=LET(origname,$A$1,origchars,MID(origname,SEQUENCE(LEN(origname)),1),fuzzchars,MID(Table1[@Fuzzstring],SEQUENCE(LEN(Table1[@Fuzzstring])),1),sortorigchars,SORT(origchars),goodchars,FILTER(fuzzchars,ISNUMBER(SEARCH(fuzzchars,origname))),goodpos,SEARCH(goodchars,Table1[@Fuzzstring]),sortfuzzchars,SORT(fuzzchars),origpos,SEARCH(origchars,origname),nextorigchar,FILTER(MID(origname,origpos+1,1),LEN(MID(origname,origpos+1,1))>0),nextfuzzchar,FILTER(MID(Table1[@Fuzzstring],goodpos+1,1),LEN(MID(Table1[@Fuzzstring],goodpos+1,1))),nextfuzzchar=nextorigchar)
This does not work if one of the good characters is missing.
Any help appreciated.