Thanks:  0
Likes:  0

# Thread: Interesting formula

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

2. 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","")

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

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

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

Code:
```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 ]

6. 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?

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

8. hello

try the following formula

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

THE STRING IS IN A1 CELL

REGARDS

9. If Tom is the last entry, then use

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

Regards!
Yogi Anand

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•