Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
The figures below relate to horseracing form which are contained within one
column. I wish to ignore the symbols '-' and '/'. 15P-P7 253-31 2F-6PP 271-21 218-23 41F7-7 9F6-23 583F-0 /2P4-1 /2-6P3 33-41P 3PP1/3 42-333 How can I extract the last three items so that they are presented in three different columns, as set out below? P P 7 3 3 1 6 P P etc. TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
One way ..
Assuming source data running in A1 down, Put in B1: =MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($A:A)),1) Copy B1 across to D1, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saxman" wrote in message ... The figures below relate to horseracing form which are contained within one column. I wish to ignore the symbols '-' and '/'. 15P-P7 253-31 2F-6PP 271-21 218-23 41F7-7 9F6-23 583F-0 /2P4-1 /2-6P3 33-41P 3PP1/3 42-333 How can I extract the last three items so that they are presented in three different columns, as set out below? P P 7 3 3 1 6 P P etc. TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
On Sat, 21 Jul 2007 11:27:42 +0100, "Saxman"
wrote: The figures below relate to horseracing form which are contained within one column. I wish to ignore the symbols '-' and '/'. 15P-P7 253-31 2F-6PP 271-21 218-23 41F7-7 9F6-23 583F-0 /2P4-1 /2-6P3 33-41P 3PP1/3 42-333 How can I extract the last three items so that they are presented in three different columns, as set out below? P P 7 3 3 1 6 P P etc. TIA With data in A1: B1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-3,1) C1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-2,1) D1: =RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),1) --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
On 21/07/2007 12:48:48, Ron Rosenfeld wrote:
With data in A1: B1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-3,1) C1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-2,1) D1: =RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),1) Thanks for the feedback. I tried Max's solution, but it did not quite work. The above is almost there, but I get the following errors. 0/12-F gives me 2 F F which should be 1 2 F 2/FP2- gives me P 2 2 which should be F P 2 23-1/ gives me 3 1 1 which should be 2 3 1 216/0- gives me 6 0 0 which should be 1 6 0 It appears that errors occur when a '-' or '/' is on the end. Don't know about the first example though? Thanks again. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
On 21/07/2007 11:55:58, "Max" wrote:
=MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($ A:A)),1) Sorry, had another go at this and it works perfectly! It has saved me a lot of effort. Thanks again. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
Thats ok. Good to hear it worked out fine for you.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Saxman" wrote Sorry, had another go at this and it works perfectly! It has saved me a lot of effort. Thanks again. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
On Sat, 21 Jul 2007 14:51:35 +0100, "Saxman"
wrote: On 21/07/2007 12:48:48, Ron Rosenfeld wrote: With data in A1: B1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-3,1) C1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),LEN(A1)-2,1) D1: =RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),1) Thanks for the feedback. I tried Max's solution, but it did not quite work. The above is almost there, but I get the following errors. 0/12-F gives me 2 F F which should be 1 2 F 2/FP2- gives me P 2 2 which should be F P 2 23-1/ gives me 3 1 1 which should be 2 3 1 216/0- gives me 6 0 0 which should be 1 6 0 It appears that errors occur when a '-' or '/' is on the end. Don't know about the first example though? Thanks again. Yup. Logical error. Here is the correction: B1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""), LEN(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""))-2,1) C1: =MID(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""), LEN(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""))-1,1) D1: (unchanged) =RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/",""),1) --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Extraction
One way ..
Assuming source data running in A1 down, Put in B1: =MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),LEN(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""))-(3-COLUMNS($A:A)),1) Copy B1 across to D1, fill down as far as required A little shorter and providing for empty cells in Column A... =IF(A1="","",CHAR(CODE(RIGHT(SUBSTITUTE(SUBSTITUTE ($A1,"-",""),"/",""),4-COLUMN(A1))))) Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selective extraction of data | Excel Discussion (Misc queries) | |||
data extraction | Excel Discussion (Misc queries) | |||
data extraction | New Users to Excel | |||
data extraction from excel | New Users to Excel | |||
Data Extraction | Setting up and Configuration of Excel |