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

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

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 <atyahoo<dotcom
----

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 <atyahoo<dotcom
----

.. 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 <atyahoo<dotcom
----

... 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 <atyahoo<dotcom
----

Max, I gotta tell you, I had given up!
I tried adding Domenic's adjusted formula
and all hell broke loose :)

I havn't tried yours yet, but it looks great,
I have 50,000 cells, which have already been
transposed with another platform, I'll
compare cells of the 50,000 and see if any abnormalities crop up.

Is there any chance you can precis your structure as
a written commentary, so I can get the gist of just
what is going on in the statement please?

At the moment, to me, it looks like Minestrone :)

Sincere thanks to all who contributed, and Max for
offering the finished product (I hope)





"Max" wrote in message ...
| .. 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 <atyahoo<dotcom
| ----
|
|

"Rodney" wrote in message
...

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

How come you didn't realise that Domenic :-)?

Good question, Bob! <VBG

In article ,
"Bob Phillips" wrote:

"Rodney" wrote in message
...

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

How come you didn't realise that Domenic :-)?

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
|
|
|
|
|

You're welcome Rodney, and if you're happy with that solution that Max gave
you, super-great!....if not, as with everything else in Excel, "there is
another way".

It uses Data TextToColumns Delimited hyphen as delimiter......and
conversion to Text on both columns, then using "ASAP Utilities" to strip off
all Letter-characters, then one simple formula copied down for the division
and to convert back to numbers....=IF(B10,A1/B1,A1)*1.....could probably all
be done with a macro, if you had to do it frequently

For me, it's easier.....

Vaya con Dios,
Chuck, CABGx3


"Rodney" wrote:

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
|
|
|
|
|

| You're welcome Rodney, and if you're happy with that solution that Max gave
| you, super-great!....if not, as with everything else in Excel, "there is
| another way".

Gee, Domenic,
I have already met with that.
I posted the solution against another offered on my Racing NG,
and I got comments like
"Far Canal!"
"Holy Spreadsheet, Batman!" etc etc.

In my usual Database NG, it was always a joy to see and
interpret different ways the spokes lead to the hub of a problem.

Best Regards
Rodney

Rodney,

I'm not sure what you meant by "I have already met with that", but
Chuck, not me, posted the message below. :)

In article ,
"Rodney" wrote:

| You're welcome Rodney, and if you're happy with that solution that Max gave
| you, super-great!....if not, as with everything else in Excel, "there is
| another way".

Gee, Domenic,
I have already met with that.
I posted the solution against another offered on my Racing NG,
and I got comments like
"Far Canal!"
"Holy Spreadsheet, Batman!" etc etc.

In my usual Database NG, it was always a joy to see and
interpret different ways the spokes lead to the hub of a problem.

Best Regards
Rodney

Oh?! sorry.
I'm one of those guys who harangues his wife for
hiding his glasses, when they're perched on top of my head all the time.
People who know me are used to it.


| Rodney,
| I'm not sure what you meant by "I have already met with that", but
| Chuck, not me, posted the message below. :)
|