small function

[Atishoo]

I have a range of cells that are a mix of blank cells and cells containing a
time, name and purpose eg "10.30 John S Golf"
there should be no more than 10 cells containing text.
i want a range of cells say b2:b12 to represent this information in time order
I have tried using the small function but this will only work for cells
containing numbers only.
Some cells will contain text only with no time I want those to appear either
at the end or the start of the list.
i think this will require a combination of the search if and small functions
but not sure quite how!!
Any suggestions gratefully received.

 

[Bob Phillips]

Extract the time with

=--LEFT(A2,FIND(" ",A2)-1)

and use SMALL on that.

 

[Atishoo]

Hi bob
thanks for that!
Am still strugling a bit as far as i can tell the left function can only be
applied to a single cell and not a range whereas im using the small function
to return the contents of the cell with the lowest time etc, It also falls
foul where there is no time and it tries to apply small to a text string
instead!
any inspiration?

 

[Bob Phillips]

It can be applied to a range of cells in an array formula.

 

[Atishoo]

so as an array should it not be:
=SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B27*B28*B29*B30,4),3)

it dont wanna work for me the swine

 

[Bob Phillips]

Why do you have *, don't you want the 3rd smallest?

You must mean

=SMALL(--(LEFT(B18:B30,4)),3)

As I said it is an array formula.

 

[Atishoo]

I thought an array formula represented the range as "B1*B2* etc!
have tried representing the range as B18:B30 as you suggest but this just
returns #value even on a simple range just containing numbers, am scratching
head!

 

[Pete_UK]

Here is one of my standard replies for array formulae:

* Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the normal ENTER. If you do this correctly then Excel
will
wrap curly braces around the formula when viewed in the formula bar -
do not type these yourself. If you edit the formula you will need to
use CSE again.

Hope this helps.

Pete

 

[Atishoo]

Many thanks Pete I have been entering using cse still not getting any joy
using the following formula

{=SMALL(LEFT(B18:B30,4),3)}

 

[Atishoo]

Ahhhhhh
it works!!! Sorry the problem was that a couple of cells in the range were
returning as #value so that was what the formula was returning.
but now I have a new problem !! i want the range to be sorted in order of
its time this just returns the time and not the rest of the contents of the
cell! i could use find to return the full cell contents based on the time
returned but thats messey! any better ideas??