Stripping and Conversion.

Here is what I hope to be a fair sample of some prices in a column,
is anyone able to offer a formula to strip the text
and convert to decimals please?

4-13F
2-5F
1-2F
8-13F
8-11F
4-5F
9-10F
1F
10-9F
5-4F
5-4
5-4F
11-8F
11-8EF
6-4F
13-8F
7-4F
7-4EF
7-4
15-8F
15-8
2F
10
250
300

Try...

=LOOKUP(9.99999999999999E+307,--LEFT(SUBSTITUTE(A1,"-","."),ROW(INDIRECT(
"1:"&LEN(A1)))))

Hope this helps!

In article ,
"Rodney" wrote:

Here is what I hope to be a fair sample of some prices in a column,
is anyone able to offer a formula to strip the text
and convert to decimals please?

4-13F
2-5F
1-2F
8-13F
8-11F
4-5F
9-10F
1F
10-9F
5-4F
5-4
5-4F
11-8F
11-8EF
6-4F
13-8F
7-4F
7-4EF
7-4
15-8F
15-8
2F
10
250
300

That is 'way beyond COOL, Domenic...........way to go!

Of course it does return #N/A if the leading character is in the A cell is
a LETTER, but your formula meets the OP's sample data beautifully. If that
unspecified condition is problematic for him, it can of course be easily
resolved with .........

=IF(ISNUMBER(LEFT(A1,1)*1),LOOKUP(9.99999999999999 E+307,--LEFT(SUBSTITUTE(A1
,"-","."),ROW(INDIRECT("1:"&LEN(A1))))),"LeadingLette r")

Vaya con Dios,
Chuck, CABGx3


"Domenic" wrote in message
...
Try...

=LOOKUP(9.99999999999999E+307,--LEFT(SUBSTITUTE(A1,"-","."),ROW(INDIRECT(
"1:"&LEN(A1)))))

Hope this helps!

In article ,
"Rodney" wrote:

Here is what I hope to be a fair sample of some prices in a column,
is anyone able to offer a formula to strip the text
and convert to decimals please?

4-13F
2-5F
1-2F
8-13F
8-11F
4-5F
9-10F
1F
10-9F
5-4F
5-4
5-4F
11-8F
11-8EF
6-4F
13-8F
7-4F
7-4EF
7-4
15-8F
15-8
2F
10
250
300

Thanks Domenic and CLR,
Have just downloaded the advice, have not tried it
(40,000 rows) and have to take off for football umpiring.
Shall return to report.
Assistance appreciated
Rodney


--

(Remove gum to reply)


"Rodney" wrote in message ...
|
| Here is what I hope to be a fair sample of some prices in a column,
| is anyone able to offer a formula to strip the text
| and convert to decimals please?
|
| 4-13F
| 2-5F
| 1-2F
| 8-13F
| 8-11F
| 4-5F
| 9-10F
| 1F
| 10-9F
| 5-4F
| 5-4
| 5-4F
| 11-8F
| 11-8EF
| 6-4F
| 13-8F
| 7-4F
| 7-4EF
| 7-4
| 15-8F
| 15-8
| 2F
| 10
| 250
| 300
|
|
|
|
|

Not bad Domenic
however...Houston, we have a problem...

My apologies for perhaps an unclear direction,
the "-" is a divisor character.

example 5-2 should end up reading 2.5 as a decimal
13-8F should end up reading 1.63 as a decimal.

Are you able to apply further legerdemaine?

Best Regards
Rodney



| Try...
|
| =LOOKUP(9.99999999999999E+307,--LEFT(SUBSTITUTE(A1,"-","."),ROW(INDIRECT(
| "1:"&LEN(A1)))))
|
| Hope this helps!
|
| In article ,
| "Rodney" wrote:
|
| Here is what I hope to be a fair sample of some prices in a column,
| is anyone able to offer a formula to strip the text
| and convert to decimals please?
|
| 4-13F
| 2-5F
| 1-2F
| 8-13F
| 8-11F
| 4-5F
| 9-10F
| 1F
| 10-9F
| 5-4F
| 5-4
| 5-4F
| 11-8F
| 11-8EF
| 6-4F
| 13-8F
| 7-4F
| 7-4EF
| 7-4
| 15-8F
| 15-8
| 2F
| 10
| 250
| 300

Assuming that Column A contains your data, enter my initial formula in
B1 and copy down. Then, enter the following formula in C1 and copy down:

=IF(ISNUMBER(SEARCH(".",B1)),LEFT(B1,SEARCH(".",B1 )-1)/RIGHT(B1,LEN(B1)-S
EARCH(".",B1)),B1)

Hope this helps!

In article ,
"Rodney" wrote:

Not bad Domenic
however...Houston, we have a problem...

My apologies for perhaps an unclear direction,
the "-" is a divisor character.

example 5-2 should end up reading 2.5 as a decimal
13-8F should end up reading 1.63 as a decimal.

Are you able to apply further legerdemaine?

Best Regards
Rodney



| Try...
|
| =LOOKUP(9.99999999999999E+307,--LEFT(SUBSTITUTE(A1,"-","."),ROW(INDIRECT(
| "1:"&LEN(A1)))))
|
| Hope this helps!
|
| In article ,
| "Rodney" wrote:
|
| Here is what I hope to be a fair sample of some prices in a column,
| is anyone able to offer a formula to strip the text
| and convert to decimals please?
|
| 4-13F
| 2-5F
| 1-2F
| 8-13F
| 8-11F
| 4-5F
| 9-10F
| 1F
| 10-9F
| 5-4F
| 5-4
| 5-4F
| 11-8F
| 11-8EF
| 6-4F
| 13-8F
| 7-4F
| 7-4EF
| 7-4
| 15-8F
| 15-8
| 2F
| 10
| 250
| 300

.. example 5-2 should end up reading 2.5 as a decimal
13-8F should end up reading 1.63 as a decimal.

Another play ... If its only the alphas E & F which may be present in the
data (data assumed in col A, A1 down), suppose you could try the formula
below in B1, format B1 to say, 2 d.p. and then copy B1 down:

=IF(IF(ISNUMBER(SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""))),SEARC H
("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")),0)=0,I F(IF(ISNUMBER(SEARCH("-
",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""))),SEARC H("-",SUBSTITUTE(SUBSTITUT
E(A1,"E",""),"F","")),0)=0,SUBSTITUTE(SUBSTITUTE(A 1,"E",""),"F",""),LEFT(SUB
STITUTE(SUBSTITUTE(A1,"E",""),"F",""),IF(ISNUMBER( SEARCH("-",SUBSTITUTE(SUBS
TITUTE(A1,"E",""),"F",""))),SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",
"")),0)-1)),IF(IF(ISNUMBER(SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","
"))),SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")),0)=0,S UBSTITUTE(SU
BSTITUTE(A1,"E",""),"F",""),LEFT(SUBSTITUTE(SUBSTI TUTE(A1,"E",""),"F",""),IF
(ISNUMBER(SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""))),SEARC H("-",S
UBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")),0)-1))/MID(SUBSTITUTE(SUBSTITUTE(A1
,"E",""),"F",""),IF(ISNUMBER(SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F"
,""))),SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")),0)+1,9 9))+0

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

Found that this particular data (in cell A7):
9-10F
seems to produce an error result of "9.00" instead of "0.90" ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

Then we'll have to change tactics...

B1, copied down:

=LEFT(A1,SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9,"-"},"
"))))

C1, copied down:

=IF(ISNUMBER(SEARCH("-",B1)),LEFT(B1,SEARCH("-",B1)-1)/RIGHT(B1,LEN(B1)-S
EARCH("-",B1)),B1)

In article ,
"Max" wrote:

Found that this particular data (in cell A7):
9-10F
seems to produce an error result of "9.00" instead of "0.90" ?
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----

... and just in case g ...
here's a sample file with the implemented formula:
http://flypicture.com/p.cfm?id=51757

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: 1_Rodney_newusers_Stripping_ConvertingData.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik atyahoodotcom
----