Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 | | | | | |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
.. 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 ---- |
#8
|
|||
|
|||
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 ---- |
#9
|
|||
|
|||
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 ---- |
#10
|
|||
|
|||
... 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 ---- |
#11
|
|||
|
|||
Thanks, Domenic !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#12
|
|||
|
|||
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 | ---- | | |
#13
|
|||
|
|||
I tried adding Domenic's adjusted formula
and all hell broke loose :) 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 :) ROTFL ! .. In the interim, try the above tweak to Domenic's suggestion .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#14
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#15
|
|||
|
|||
........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) "Max" wrote in message ... | 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 <atyahoo<dotcom | ---- | | |
#16
|
|||
|
|||
You're welcome !
Thanks for the feedback .. Btw ... 1° 22' N 103° 45' E = "Singapore" <g How about you ? -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Rodney" wrote in message ... .......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) |
#17
|
|||
|
|||
"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 :-)? |
#18
|
|||
|
|||
In article ,
"Max" wrote: 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 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. |
#19
|
|||
|
|||
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 :-)? |
#20
|
|||
|
|||
You're welcome, Domenic !
Thanks for the refinements .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#21
|
|||
|
|||
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 | | | | | |
#22
|
|||
|
|||
Roughly
latitude 30 06 South and longitude 116 38 East (Perth, Western Australia) (IIRC once voted the most boring city in Australia, and.........I like it that way :) I'll wave, next time I pass over on my way to Bkk Rgds Rodney | You're welcome ! | Thanks for the feedback .. | | Btw ... 1° 22' N 103° 45' E = "Singapore" <g | How about you ? |
#23
|
|||
|
|||
| 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 |
#24
|
|||
|
|||
Thanks ! Looks like we're in the same time zone
I'll wave, next time I pass over on my way to Bkk Just drop a note when you' re somewhere over 1° 22' N 103° 45' E, and with some luck, it'll land within my reach <bg -- Rgds Max xl 97 --- GMT+8, xdemechanik <atyahoo<dotcom ---- |
#25
|
|||
|
|||
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 |
#26
|
|||
|
|||
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. :) | |
#27
|
|||
|
|||
Here's a new link to the revised sample file
with the decomposed formulas in Sheet2: http://www.savefile.com/files/9169505 File: 2_Rodney_newusers_Stripping_ConvertingData.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#28
|
|||
|
|||
Gee, I thought I may be enjoying deja-vu there Max :)
I'll have a dekko Thanks. Can "Save File" be used to post images? I often need to post Postage Stamp Images on the web, (short time usage) and wondered if this may suffice? Cheers "Max" wrote in message ... | Here's a new link to the revised sample file | with the decomposed formulas in Sheet2: | http://www.savefile.com/files/9169505 | File: 2_Rodney_newusers_Stripping_ConvertingData.xls | -- | Rgds | Max | xl 97 | --- | GMT+8, 1° 22' N 103° 45' E | xdemechanik <atyahoo<dotcom | ---- | | |
#29
|
|||
|
|||
aha , you're still tracking this thread ? <bg
(the post was meant for the archives) Can "Save File" be used to post images? Probably so, but do check out the details/info on their home page .. I just chanced upon the new site since discovering recently that the previous file host (flypicture) appears to have removed all the sample files posted, some a bit prematurely, it seems .. cheers. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|