Find max value in a single cell with multiple value in it

[nicnad]

Hi,

I have a single cell that contain the following value separated by commas :

2009 - Q1, 2009 - Q2, 2010 - Q1, 2010 - Q2, 2010 - Q3, 2010 - Q4, 2011 - Q1, 2011 - Q2

What is the function to return only the value 2011 - Q2 (by this I mean the latest value when sorted on A to Z)

Thank you.

 

[Excelestial]

If the series is already sorted within the cell than...

<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=627 border=0><COLGROUP><COL style="WIDTH: 470pt; mso-width-source: userset; mso-width-alt: 22930" width=627><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2866509 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 470pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=627 height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2009 - Q1, 2009 - Q2, 2010 - Q1, 2010 - Q2, 2010 - Q3, 2010 - Q4, 2011 - Q1, 2011 - Q2

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2866509 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> =IF(ISERROR(SEARCH("2011 - Q2",F6,1)),"","2011 - Q2")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> =RIGHT(F6,9)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD></TR></TBODY></TABLE>

 

[Excelestial]

...I'd use the last formula (the RIGHT function).

 

[jasonb75]

For any dates, in any order, where A1 is the cell holding the string of dates

=TEXT(MAX(--MID(SUBSTITUTE(A1," - Q",""),(ROW(INDIRECT("1:"&INT((LEN(A1)+2)/11)))-1)*7+1,5)),"0000 - Q0")

Note, this formula must be array confirmed using Shift Ctrl & Enter!

If this is not done the formula will incorrectly return the first date to the left of the string.

 

[nicnad]

I already have a function that sort the data and put it in one cell so RIGHT function would work but How do I find the number of arguments (i.e. 9) by function.

Example :

<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=627 border=0><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2009 - Q1, 2009 - Q2, 2010 - Q1, 2010 - Q2, 2010 - Q3, 2010 - Q4, 2011 - Q1, 2011 - Q2

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2866509 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-TOP-COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>=RIGHT(F6,9)</TD></TR></TBODY></TABLE>

How do I find the 9 by function

 

[nicnad]

Right function doesn't work.

Assuming you would juste have words separated by a comma in a single cell : apple, banana, kiwi

How can I find the word after the 2nd comma? (i.e. kiwi)

 

[Aladin Akyurek]

I already have a function that sort the data and put it in one cell so RIGHT function would work but How do I find the number of arguments (i.e. 9) by function.

Example :

<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=627><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>2009 - Q1, 2009 - Q2, 2010 - Q1, 2010 - Q2, 2010 - Q3, 2010 - Q4, 2011 - Q1, 2011 - Q2


</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2866509 class=xl63 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 15pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>=RIGHT(F6,9)</TD></TR></TBODY></TABLE>

How do I find the 9 by function

Try something like:

=TRIM(IF(ISNUMBER(FIND(",",F9)),
    REPLACE(F9,1,FIND("#",SUBSTITUTE(F9,",","#",
     LEN(F9)-LEN(SUBSTITUTE(F9,",","")))),""),F9))

 

[nicnad]

Try something like:

=TRIM(IF(ISNUMBER(FIND(",",F9)),
    REPLACE(F9,1,FIND("#",SUBSTITUTE(F9,",","#",
     LEN(F9)-LEN(SUBSTITUTE(F9,",","")))),""),F9))

I used (LEN(E3)-LEN(SUBSTITUTE(E3,",",""))) to find the number of commas. Lets say I have the follow words (they can contains spaces) in a single cell : banana, George W. Bush, kiwi.

I find 2 with the function stated above but how can I find the word kiwi, based on the fact that it appears after the 2nd comma?

 

[Rick Rothstein]

Assuming you would juste have words separated by a comma in a single cell : apple, banana, kiwi

How can I find the word after the 2nd comma? (i.e. kiwi)

Specifically, this formula...

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

The more general form of this formula is this...

=TRIM(MID(SUBSTITUTE(A1,<<Delimiter>>,REPT(" ",LEN(A1))),LEN(A1)*(<<FieldNumber>>-1),LEN(A1)))

where you would replace the items in double angle brackets with what they indicate should go there.

 

[Aladin Akyurek]

I used (LEN(E3)-LEN(SUBSTITUTE(E3,",",""))) to find the number of commas. Lets say I have the follow words (they can contains spaces) in a single cell : banana, George W. Bush, kiwi.

I find 2 with the function stated above but how can I find the word kiwi, based on the fact that it appears after the 2nd comma?

The full formula will give you kiwi if F9 houses:

banana, George W. Bush, kiwi

Or if E3 houses the foregoing string:

In D3 enter:

=TRIM(IF(ISNUMBER(FIND(",",E3)),
    REPLACE(E3,1,FIND("#",SUBSTITUTE(E3,",","#",
     LEN(E3)-LEN(SUBSTITUTE(E3,",","")))),""),E3))