Interesting formula

[zacemmel]

I want to return the name "Tom" from cell A1 where cell A1 contains "Harry, Sally, Bob, Jack, Tom."

I am just curious how other board members have approached this problem.

[Ricky Morris]

On 2002-05-01 13:59, zacemmel wrote:
I want to return the name "Tom" from cell A1 where cell A1 contains "Harry, Sally, Bob, Jack, Tom."

I am just curious how other board members have approached this problem.

If I understand you correctly I would do:

=If(or(a1="Harry",a1="Sally",a1="Bob",a1="Jack",a1="Tom"),"Tom","")

[Asala42]

Not sure on your exact situation, but say "Tom" is a value in B1 and your string is in A1:

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))>0,B2,"Value in B2 not in string")

Hope that helps,
Adam

[zacemmel]

I want to return the last name of the series in that cell, in that case it was Tom.

[Jay Petrulis]

I would write a UDF to find the index name of my choosing.

Function NAMEFIND(RefStr As Range, intIndex As Integer)
Dim NameCt As Integer
Dim Position() As Integer
Dim NameList()
Dim x As Integer

' Find the number of string separators
NameCt = Len(Trim(RefStr)) - Len(WorksheetFunction.Substitute(Trim(RefStr), ",", "")) + 1
If NameCt = 1 Then
    NAMEFIND = RefStr
    Exit Function
End If


ReDim Position(1 To NameCt - 1)
For x = 1 To NameCt - 1
    If x = 1 Then
        Position(x) = WorksheetFunction.Search(",", Trim(RefStr), 1)
    Else
        Position(x) = WorksheetFunction.Search(",", Trim(RefStr), Position(x - 1) + 1)
    End If
Next x
ReDim NameList(1 To NameCt)
For x = 1 To NameCt
    If x = 1 Then
        NameList(x) = Left(RefStr, Position(x) - 1)
    ElseIf x = NameCt Then
        NameList(x) = Right(Trim(RefStr), Len(Trim(RefStr)) - Position(x - 1) - 1)
    Else
        NameList(x) = Mid(Trim(RefStr), Position(x - 1) + 1, Position(x) - Position(x - 1) - 1)
    End If
Next x

NAMEFIND = Trim(NameList(intIndex))

End Function

If the string was in A1, the following returned Tom

=NAMEFIND(A1,5)

Bye,
Jay

[ This Message was edited by: Jay Petrulis on 2002-05-01 14:33 ]

[zacemmel]

Does that return the last name of the series in the cell? Like if the series were, "Sally, Jim, Zac, Jeff, Brad" would it return Brad?

[Jay Petrulis]

Hi Zac,

Do you need to always return only the last name found? What I posted would be to find any index number you want.

=FINDNAME(A1,4)
would return Jack (or Jeff in the 2nd string).

[sen_edp]

hello

try the following formula

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

THE STRING IS IN A1 CELL

REGARDS

[Yogi Anand]

If Tom is the last entry, then use

=MID(A1,FIND("Tom",A1,1),3)

Regards!
Yogi Anand

[Aladin Akyurek]

I see the initial specs, for which Adam provided the answer, are changed. Given new or more precise specs, try:

=REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),"")

where A1 houses a value like:

Harry, Sally, Bob, Jack, Tom

That is, no dot at the end of string and names are separated by a single space.

Aladin