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.

Extract the time with

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

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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.

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" wrote:

Extract the time with

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

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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.

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

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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" wrote:

Extract the time with

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

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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.

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

it dont wanna work for me the swine


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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" wrote:

Extract the time with

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

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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.

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.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
so as an array should it not be:
=SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3)

it dont wanna work for me the swine


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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" wrote:

Extract the time with

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

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
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.