Is it possible to parse the data below (including brackets), so that it
appears as set out further down. The first in the list is in cell A2.

Solent 36
Group Captain 29
Before You Go 23
Purple Moon 37
Halla San 29
Strategic Mount 324
John Terry 16
Celtic Spirit 32
Misty Dancer 36
Mull Of Dubai 24
Dan Dare 29
High Treason 16
Instructor 84 (59J)
Masterofthecourt 11
.............................

Solent
Group Captain
Before You Go
Purple Moon
Halla San
Strategic Mount
John Terry
Celtic Spirit
Misty Dancer
Mull Of Dubai
Dan Dare
High Treason
Instructor
Masterofthecourt

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

The above will do it, but gets rid of everything except the first word.

Is it possible to capitalise the results?

Thanks.

Try:

=UPPER(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789"))-2))

"Saxman" wrote:

Is it possible to parse the data below (including brackets), so that it
appears as set out further down. The first in the list is in cell A2.

Solent 36
Group Captain 29
Before You Go 23
Purple Moon 37
Halla San 29
Strategic Mount 324
John Terry 16
Celtic Spirit 32
Misty Dancer 36
Mull Of Dubai 24
Dan Dare 29
High Treason 16
Instructor 84 (59J)
Masterofthecourt 11
.............................

Solent
Group Captain
Before You Go
Purple Moon
Halla San
Strategic Mount
John Terry
Celtic Spirit
Misty Dancer
Mull Of Dubai
Dan Dare
High Treason
Instructor
Masterofthecourt

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

The above will do it, but gets rid of everything except the first word.

Is it possible to capitalise the results?

Thanks.

While the original data didn't include any without trailing numbers (e.g.,
"Solent") or without a space between the trailing number and the text that
comes before it (e.g., "Solent36"), you can generalize your function to
handle the given data plus these variations by changing the subtraction of 2
to a subtraction of 1 and using the TRIM function...

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9} ,A2&"0123456789"))-1)))

Rick


"Toppers" wrote in message
...
Try:

=UPPER(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789"))-2))

"Saxman" wrote:

Is it possible to parse the data below (including brackets), so that it
appears as set out further down. The first in the list is in cell A2.

Solent 36
Group Captain 29
Before You Go 23
Purple Moon 37
Halla San 29
Strategic Mount 324
John Terry 16
Celtic Spirit 32
Misty Dancer 36
Mull Of Dubai 24
Dan Dare 29
High Treason 16
Instructor 84 (59J)
Masterofthecourt 11
.............................

Solent
Group Captain
Before You Go
Purple Moon
Halla San
Strategic Mount
John Terry
Celtic Spirit
Misty Dancer
Mull Of Dubai
Dan Dare
High Treason
Instructor
Masterofthecourt

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

The above will do it, but gets rid of everything except the first word.

Is it possible to capitalise the results?

Thanks.

Rick Rothstein (MVP - VB) wrote:
While the original data didn't include any without trailing numbers
(e.g., "Solent") or without a space between the trailing number and the
text that comes before it (e.g., "Solent36"), you can generalize your
function to handle the given data plus these variations by changing the
subtraction of 2 to a subtraction of 1 and using the TRIM function...

=UPPER(TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9} ,A2&"0123456789"))-1)))

Rick

This works beautifully.

Thanks to all.