Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Interesting formula

  1. #1
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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","")
    It's never too late to learn something new.

    Ricky

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,312
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If Tom is the last entry, then use

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

    Regards!
    Yogi Anand

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,038
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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