Results 1 to 9 of 9

Thread: Get corresponding text

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

    Get corresponding text

    Hi All,



    I have text like : name,age,phone,city,Prince,26,455666,delhi in cell A1
    What i need to do is i have a cell C1 in which if i write Name the result should "Prince" In cell D1 , If i write age then it should be 26 and so On.



    Thanks in Advance.

  2. #2
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    12
    Use the text to columns feature in the Data section of Excel. Your delimiter is comma.

    Alan

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try the attachment.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    =LEFT(MID(A1,FIND("|",SUBSTITUTE(A1,",","|",LEN(LE FT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",",SEARCH(",",MID(A1,FIND("|",SUBSTITU TE(A1,",","|",LEN(LEFT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",")-1)

    Regards,
    Excel Fox
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    =LEFT(MID(A1,FIND("|",SUBSTITUTE(A1,",","|",LEN(LE FT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",",SEARCH(",",MID(A1,FIND("|",SUBSTITU TE(A1,",","|",LEN(LEFT(","&A1,FIND(C1,","&A1)-1))-LEN(SUBSTITUTE(LEFT(","&A1,FIND(C1,","&A1)-1),",",""))+(LEN(","&A1)-LEN(SUBSTITUTE(A1,",","")))*0.5-1))+1,255)&",")-1)
    Your formula appears to work only in XL2007 and above (too many nested levels for XL2003 I presume). In any event, here is a shorter formula that appears to work correctly in XL2003 and above...

    =IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(5+LEN(LEFT(A1,SEARCH(C1,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(C1,A1)-1),",","")))*999-998,999)),"")

    Note that I added a test for C1 being blank to suppress the value that otherwise gets displayed.
    Last edited by Rick Rothstein; 04-18-2012 at 08:48 AM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    Your formula appears to work only in XL2007 and above (too many nested levels for XL2003 I presume). In any event, here is a shorter formula that appears to work correctly in XL2003 and above...

    =IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(5+LEN(LEFT(A1,SEARCH(C1,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(C1,A1)-1),",","")))*999-998,999)),"")

    Note that I added a test for C1 being blank to suppress the value that otherwise gets displayed.
    **** CORRECTION ****

    While the quoted formula works, it does not duplicate the functionality of your formula exactly. Your formula adapts to a varying number of fields in A1 whereas my formula assumed 4 fields (and 4 data for those fields) was constant. The below formula, a little longer than my original formula, but still shorter than yours (and it still works in XL2003 as well), duplicates the functionality of your formula exactly (well, except for when C1 is blank)...

    =IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/2+LEN(LEFT(A1,SEARCH(C1,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(C1,A1)-1),",","")))*999-998,999)),"")

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    **** CORRECTION ****

    While the quoted formula works, it does not duplicate the functionality of your formula exactly. Your formula adapts to a varying number of fields in A1 whereas my formula assumed 4 fields (and 4 data for those fields) was constant. The below formula, a little longer than my original formula, but still shorter than yours (and it still works in XL2003 as well), duplicates the functionality of your formula exactly (well, except for when C1 is blank)...

    =IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/2+LEN(LEFT(A1,SEARCH(C1,A1)-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(C1,A1)-1),",","")))*999-998,999)),"")
    **** CORRECTION #2 ****

    Just noticed a flaw in both of our formulas... if the field name being searched for is embedded as text within an earlier field name, the data for that earlier field name will be found first. For example, if A1 contained this...

    name,mortgage,age,Bob,Yes,26

    both our formulas would see the letters "age" in mortgage and return Yes whether C1 contained age or mortgage. Here is my code corrected to remove this flaw...

    =IF(LEN(C1),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(1+(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/2+LEN(LEFT(A1,SEARCH(","&C1&",",","&A1&",")-1))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(","&C1&",",","&A1&", ")-1),",","")))*999-998,999)),"")

    Oh, and one other difference between our formulas... yours is case sensitive while mine is not.

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Select A1, hit Ctrl+F3, Define String , Refers to: =EVALUATE("{"""&SUBSTITUTE(Sheet1!$A1,",",""";""") &"""}") , Click OK

    In D1

    =INDEX(String,MATCH(C1,String,0)+COUNTA(String)/2)
    Last edited by Admin; 04-19-2012 at 07:27 AM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Member Rajan_Verma's Avatar
    Join Date
    Sep 2011
    Posts
    81
    Rep Power
    13
    One VBA Solution here to Split Range in Array.

    You just need to Use formula =SplitMe(Range,RowNum,ColNum)

    Code:
    Function SplitMe(rng As Range, lngRow As Long, lngCol As Long) As Variant
        
        Dim VarArr()
        Dim VarArr2
        Dim lngRowCount As Long
        Dim lngColCount As Long
        Dim lngCounter
        
        lngCounter = 0
        VarArr2 = Split(rng.Value, ",")
        ReDim VarArr(0 To lngRow - 1, 0 To lngCol - 1)
        For lngColCount = 0 To lngCol - 1
            For lngRowCount = 0 To lngRow - 1
                VarArr(lngRowCount, lngColCount) = VarArr2(lngCounter)
                lngCounter = lngCounter + 1
            Next
        Next
        SplitMe = VarArr
        
    End Function
    Rajan

Tags for this Thread

Posting Permissions

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