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