If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Thanks, Domenic !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- |
#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 atyahoodotcom | ---- | | |
#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 atyahoodotcom ---- |
#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 atyahoodotcom ---- |
#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 atyahoodotcom | ---- | | |
#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 atyahoodotcom ---- "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 atyahoodotcom ---- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ASC save stripping characters | dennis | General Discussion | 2 | July 7th, 2004 12:22 AM |