Stripping and Conversion.

  • Thread starter Thread starter Rodney
  • Start date Start date
R

Rodney

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!
 
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.99999999999999E+307,--LEFT(SUBSTITUTE(A1
,"-","."),ROW(INDIRECT("1:"&LEN(A1))))),"LeadingLetter")

Vaya con Dios,
Chuck, CABGx3
 
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


--
(e-mail address removed)
(Remove gum to reply)


|
| 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 <[email protected]>,
|
| > 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!
 
.. example 5-2 should end up reading 2.5 as a decimal
13-8F should end up reading 1.63 as a decimal.
Click to expand...

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",""))),SEARCH
("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")),0)=0,IF(IF(ISNUMBER(SEARCH("-
",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""))),SEARCH("-",SUBSTITUTE(SUBSTITUT
E(A1,"E",""),"F","")),0)=0,SUBSTITUTE(SUBSTITUTE(A1,"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,SUBSTITUTE(SU
BSTITUTE(A1,"E",""),"F",""),LEFT(SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""),IF
(ISNUMBER(SEARCH("-",SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F",""))),SEARCH("-",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,99))+0
 
Found that this particular data (in cell A7):
9-10F
seems to produce an error result of "9.00" instead of "0.90" ?
 
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)
 
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)





| .. 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 <at>yahoo<dot>com
| ----
|
|
 
I tried adding Domenic's adjusted formula
and all hell broke loose :)
Click to expand...

Perhaps try adding a "+0" to Domenic's formula for C1, viz. use in C1:

=IF(ISNUMBER(SEARCH("-",B1)),LEFT(B1,SEARCH("-",B1)-1)/RIGHT(B1,LEN(B1)-SEAR
CH("-",B1)),B1)+0

and copy down as before

The "+0" will coerce all text numbers to real numbers. And hopefully, this
might be enough to smoothen the downstream calcs, and get you on your way.
For the sample list in your post, with the coercion in place, think both
Domenic's and my suggestion will now return the same results.
... At the moment, to me, it looks like Minestrone :)
Click to expand...
ROTFL ! .. In the interim, try the above tweak to Domenic's suggestion ..
 
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 :)
Click to expand...

With the source data in A1 down,
Formulas in B1:F1 (copied down) are :
1. =SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")
2. =IF(ISNUMBER(SEARCH("-",B1)),SEARCH("-",B1),0)
3. =IF(C1=0,B1,LEFT(B1,C1-1))
4. =MID(B1,C1+1,99)
5. =IF(C1=0,D1,D1/E1)+0

The progressive intents of the formulas are ... :
1. Substitute alphas (E,F) in source string with blanks: ""
2. Search for position of dash: "-". If there's no dash, return a zero "0"
3. Extract the number to the LEFT of the dash
4. Extract the number to the RIGHT of the dash (MID is used)
5. Do the division, i.e. [step3] over [step4], or if there's no dash, just
return the number. Coerce any resulting text number with a "+0"

Here's a revised sample file with the decomposed formulas in Sheet2:
http://flypicture.com/p.cfm?id=51785

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

File: 2_Rodney_newusers_Stripping_ConvertingData.xls
 
........and a very fine piece of cobbling Max.
I'll carry out my usual reverse engineering,
hash it around and see how it all works.
Thanks for bearing with us.

When I have a spare moment I'll work out
where your'e coming from.(GMT addy)


| > 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 :)
|
| With the source data in A1 down,
| Formulas in B1:F1 (copied down) are :
| 1. =SUBSTITUTE(SUBSTITUTE(A1,"E",""),"F","")
| 2. =IF(ISNUMBER(SEARCH("-",B1)),SEARCH("-",B1),0)
| 3. =IF(C1=0,B1,LEFT(B1,C1-1))
| 4. =MID(B1,C1+1,99)
| 5. =IF(C1=0,D1,D1/E1)+0
|
| The progressive intents of the formulas are ... :
| 1. Substitute alphas (E,F) in source string with blanks: ""
| 2. Search for position of dash: "-". If there's no dash, return a zero "0"
| 3. Extract the number to the LEFT of the dash
| 4. Extract the number to the RIGHT of the dash (MID is used)
| 5. Do the division, i.e. [step3] over [step4], or if there's no dash, just
| return the number. Coerce any resulting text number with a "+0"
|
| Here's a revised sample file with the decomposed formulas in Sheet2:
| http://flypicture.com/p.cfm?id=51785
|
| (Right-click on the link: "Download File"
| at the top in the page, just above the ads)
|
| File: 2_Rodney_newusers_Stripping_ConvertingData.xls
| --
| Rgds
| Max
| xl 97
| ---
| GMT+8, 1° 22' N 103° 45' E
| xdemechanik <at>yahoo<dot>com
| ----
|
|
 
You're welcome !
Thanks for the feedback ..

Btw ... 1° 22' N 103° 45' E = "Singapore" <g>
How about you ?
 
Max said:
Perhaps try adding a "+0" to Domenic's formula for C1, viz. use in C1:

=IF(ISNUMBER(SEARCH("-",B1)),LEFT(B1,SEARCH("-",B1)-1)/RIGHT(B1,LEN(B1)-SEAR
CH("-",B1)),B1)+0
Click to expand...

Max, just a tweak on your tweak... :)

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

Since the result arising from the second argument of the IF function is
already a numerical value, only the result arising from the third
argument need be coerced.

Thanks for catching my oversight on both my original formula and this
one.
 
Back
Top