Data Extraction

  • Thread starter Thread starter Saxman
  • Start date Start date
S

Saxman

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
 
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
 
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
Click to expand...

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
 
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)
Click to expand...

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.
 
=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.
 
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.
Click to expand...

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
 
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
Click to expand...

A little shorter and providing for empty cells in Column A...

=IF(A1="","",CHAR(CODE(RIGHT(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"/",""),4-COLUMN(A1)))))

Rick
 
Back
Top