|
Parsing
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. |
Parsing
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. |
Parsing
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. |
Parsing
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. |
| All times are GMT +1. The time now is 06:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com