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.

How can I compare a list of strings, allowing positive matching b.
 

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.

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.