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

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

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

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.

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.

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.

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

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