Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I compare a list of strings, allowing positive matching b.
I'm interested in being able to compare strings, and returning a positive
match has been found (e.g. tree = trees). Just stripping of the last "s" does not work when irregular plurals are included (e.g. consider geese and goose, matches and match). I'm interested in a function similar to VLOOKUP, but with this added functionality. Is there a way of consulting a complete list of plurals, contained in a library, or something similar? The English language can be a bit of a bitch sometimes. Has anyone ever encountered anything similar?? I'm using MS EXCEL 2003 (PC), if that helps. Many thaks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I compare a list of strings, allowing positive matchin
Thanks Frank,
I've actually found a solution: simple UDF in EXCEL. Needs improving, but works OK. Sorry if it's coded a bit badly! ' Convert the input word from plural to singular Function PluralToSingular(ByVal plural As String) As String ' convert to lowercase for easier comparison Dim lower As String Dim res As String lower = LCase(plural) ' rule out a few exceptions If lower = "feet" Then res = "Foot" ElseIf lower = "geese" Then res = "Goose" ElseIf lower = "men" Then res = "Man" ElseIf lower = "women" Then res = "Woman" ElseIf lower = "criteria" Then res = "Criterion" ' plural uses "ies" if word ends with "y" preceeded by a non-vowel ElseIf Right(lower, 3) = "ies" Then ' AndAlso "aeiou".IndexOf(lower.Substring(lower.Length - 4, 1)) < 0 res = Left(plural, Len(plural) - 3) & "y" ElseIf Right(lower, 2) = "es" Then If Right(lower, 3) = "ees" Then res = Left(plural, Len(plural) - 1) Else res = Left(plural, Len(plural) - 2) End If Else res = Left(plural, Len(plural) - 1) End If res = LCase(res) ' the result must preserve the original word's capitalization If plural = lower Then PluralToSingular = LCase(res) ' it was an all-lowercase word ElseIf plural = UCase(plural) Then PluralToSingular = UCase(res) ' it was an all-uppercase word Else PluralToSingular = res ' return whatever is in "res" End If End Function "Frank Kabel" wrote: Hi this is IMHO not a task which should be performed in Excel. If you really want to consider such plurals you would first need a dictionary with all these terms and afterwards an application to match such words. I would guess that there're some third party applications available. Maybe start with a Google search (though probably all of these applications will cost some money) -- Regards Frank Kabel Frankfurt, Germany "Joe Southin" <Joe schrieb im Newsbeitrag ... I'm interested in being able to compare strings, and returning a positive match has been found (e.g. tree = trees). Just stripping of the last "s" does not work when irregular plurals are included (e.g. consider geese and goose, matches and match). I'm interested in a function similar to VLOOKUP, but with this added functionality. Is there a way of consulting a complete list of plurals, contained in a library, or something similar? The English language can be a bit of a bitch sometimes. Has anyone ever encountered anything similar?? I'm using MS EXCEL 2003 (PC), if that helps. Many thaks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Two Text Strings to see if Like | Excel Worksheet Functions | |||
matching columns using text strings! | Excel Worksheet Functions | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
How to check matching strings in 3 columns | Excel Discussion (Misc queries) | |||
Compare cells/columns and highlight matching text strings | Excel Worksheet Functions |