# how to compare texts in excel?

I have a list of German words, and I want to weed out the inflected forms. The words are in alphabetical order. For example:

• ähnlich
• ähnliche
• ahnung

So next to ähnlich I would like to see 1, as it is only 1 letter different compared to ähnliche, but 3 next to ähnliche because it has 3 letters not found in that order in ahnung.

I know there are words that are only 1 letter different, and mean completly unrelated stuff, I only need help from excel, I know it can not solve it on its own.

-
Where do you draw the line between similar or not similar? What would be the rule to do that? –  slhck Jan 23 '13 at 12:23
Are you asking for an automated way to do this? Like a thesarus would look them up and decide which was the better word? If so, I doubt it can be done in Excel as there is no way the third party software could know which is the best word –  Dave Jan 23 '13 at 12:32
You could approximate by checking against a list of typical 'suffixes': -e -es -er -est etc. Whatever programmatic approach you take will probably always need a set of human eyeballs verifying the output though. –  Andi Mohr Jan 23 '13 at 12:37

If I understand what you’re asking (and that’s a very big “IF”), you want something like this:

```=IF(LEFT(A1,9)=LEFT(A2,9), LEN(A2)-9,
IF(LEFT(A1,8)=LEFT(A2,8), LEN(A2)-8,
IF(LEFT(A1,7)=LEFT(A2,7), LEN(A2)-7,
IF(LEFT(A1,6)=LEFT(A2,6), LEN(A2)-6,
IF(LEFT(A1,5)=LEFT(A2,5), LEN(A2)-5,
IF(LEFT(A1,4)=LEFT(A2,4), LEN(A2)-4,
IF(LEFT(A1,3)=LEFT(A2,3), LEN(A2)-3,
IF(LEFT(A1,2)=LEFT(A2,2), LEN(A2)-2,
IF(LEFT(A1,1)=LEFT(A2,1), LEN(A2)-1,
LEN(A2) )))))))))
```

In other words, find the longest common initial substring of `A1` and `A2`, and then return how many characters `A2` has beyond that.  This would be a terrible kludge if it worked, and it doesn’t, because Excel doesn’t let you nest that deep, and you would need to go up to … what, 42?  I know that some German words are very long.  (I did test the above formula by deleting the first two lines and starting with `7`.)  This clearly is something that should be done in VBA, and it would be a struggle for me to write it, so I’ll ask the rest of the SU community to give the VBA answer.  (Or, András, if this isn’t what you want, explain what you do want more clearly.)

-