extract data between two comma

Question

102,34112,021502503130010010000,QAR,1,24,1,{NULL:0},{5:Y},6,14.5,31-MAR-2011

above data is in one cell , i want to extract the data between second and third comma.

im using this formula =MID(A1,FIND(",",A1),(FIND(",",A1,5))-(FIND(",",A1)))

but i am looking for some thing simple ???

Answer 1

102,34112,021502503130010010000,QAR,1,24,1,{NULL:0},{5:Y},6,14.5,31-MAR-2011

 

above data is in one cell , i want to extract the data between second and third comma.

im using this formula =MID(A1,FIND(",",A1),(FIND(",",A1,5))-(FIND(",",A1)))

but i am looking for some thing simple ???

hi,

I doin't think this is simpler but it's a lot more flexible. It will extract any set of data between commas by simply changing the 2 towards the end of the formula. The 2 in this case makes it extract the second set of data

=SUBSTITUTE(MID(SUBSTITUTE("," & A2&REPT(" ",6),",",REPT(",",255)),2*255,255),",","")

Answer 2

That's perfectly flexible. but a way too complicated. Need something to remember on fingertips.

Then; Like you, I'll wait for someone to post it<g>

Answer 3

That's perfectly flexible. but a way too complicated. Need something to remember on fingertips.

Answer 4

You could use a VBA function such as

Function SplitString(ST As String, Item As Integer) As Variant

  SplitString = Split(ST, ",")(Item - 1)

End Function

and enter

=SplitString(A1,2)