Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Changed because of the comments.

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.

share|improve this question
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
up vote 0 down vote accepted

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.)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .