Get corresponding text

[princ_wns]

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.

[alansidman]

Use the text to columns feature in the Data section of Excel. Your delimiter is comma.

Alan

[Excel Fox]

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

[Rick Rothstein]

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

[Rick Rothstein]

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

[Rick Rothstein]

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

[Excel Fox]

Try the attachment.

[Admin]

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)

[Rajan_Verma]

One VBA Solution here to Split Range in Array.

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

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