Interesting formula

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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","")
 
Upvote 0
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
 
Upvote 0
I want to return the last name of the series in that cell, in that case it was Tom.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
hello

try the following formula

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

THE STRING IS IN A1 CELL

REGARDS
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top