Using Max function in mixed cell

[LB79]

Hello,

Im having trouble using MAX in a mixed cell.

My data in column B for example is:

201301 January
201302 February
etc

Using =Max(B:B) however returns a 0 value.

Can anyone help?

Many thanks

[Fotis1991]

oNE SIMPLE WAY IS USING A HELPER AND HIDDEN COLUMN. lET'S SAY COLUMN c.

In C1 and copy down use this.

=LEFT(B1,6)+0

Then use (MAX(C:C)

[LB79]

Thanks - Ive tried to combime into a single formula but it #Value's out.
Is there no way to do it without adding a new column and increasing the amount of data in the spreadsheet?

[BenMiller]

=MAX(--LEFT(B:B,6)) confirmed with CTRL+SHIFT+ENTER (array formula)

[LB79]

Thanks for the suggestion - the array formula returns a 0 value

[BenMiller]

Thanks for the suggestion - the array formula returns a 0 value

Change B:B to your actual data range. Assuming there are no blank cells.

[Fotis1991]

ARRAY formula

=MAX(--LEFT(B:B,6))

But i insist that with helper column is better.

[LB79]

Thanks Ben - Seems to have resolved it and now works perfectly!

Fotis - Thanks also for your suggestion - I think adding an additional column of data should be avoided where possible.

[Fotis1991]

Fotis - Thanks also for your suggestion - I think adding an additional column of data should be avoided where possible.

You are welcome. What you say is the one side of the coin. The other side, says that it's better to keep the formulas as much simple as you can, specially if you handle big files. And helper columns help us to keep our data simple and not heavy!

[BenMiller]

Thanks Ben - Seems to have resolved it and now works perfectly!

No problem ... glad to help Appreciate the feedback!