Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old May 23rd 05, 12:46 AM
Max
 
Posts: n/a
Default

Thanks, Domenic !
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----



  #12   Report Post  
Old May 23rd 05, 02:11 AM
Rodney
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 02:26 AM
Max
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 03:22 AM
Max
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 06:04 AM
Rodney
 
Posts: n/a
Default


........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   Report Post  
Old May 23rd 05, 07:13 AM
Max
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 09:41 AM
Bob Phillips
 
Posts: n/a
Default


"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   Report Post  
Old May 23rd 05, 01:24 PM
Domenic
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 01:26 PM
Domenic
 
Posts: n/a
Default

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   Report Post  
Old May 23rd 05, 01:57 PM
Max
 
Posts: n/a
Default

You're welcome, Domenic !
Thanks for the refinements ..
--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:08 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017