Gates Is Antichrist
Well-known Member
- Joined
- Aug 15, 2002
- Messages
- 1,961
This is a restart of http://www.mrexcel.com/board2/viewtopic.php?t=130209&postdays=0&postorder=asc&start=0 which grew long but didn't solve the issue. Perhaps this is related to the old dilemma of trying to preserve trailing zeroes?
Imported cell A1 is
=4-6
I cannot figure out how to stop Excel from performing the math. I want to parse out the 4 and 6, but Excel changes it to minus 2.
C1 ="X" & A1
gives X-2
Formatting C1 as text causes it to look like
="X" & A1
(that's actually what's displayed - it even treats the equal sign as text).
This still doesn't give me the 4 and 6 to work with.
Other things I've tried all perform the subtraction. Even MID(A1,1,1) returns "-" - CHEEEEZ!! *** Even if the cells I paste to are formatted as text *** before the paste, there are still cases where Excel "helps" me by making a date or performing subtraction:
Try this test:
format a new sheet's cells A1 to C3 as text;
scrape-copy only the 3 characters 4-6 on this line in this message with Ctl-C;
Ctl-V to A1. Nasty, huh? Are the 4 and the 6 lost forever then?
The Death Example:
Take http://mlb.mlb.com/NASApp/mlb/mlb/standings/index.jsp and scrape and ctl-C the 5 line rectangle, starting your scrape with the left edge of the letter N in New York, through the end of the Toronto line. Paste into a virgin worksheet (general format). You'll see that some cells became dates, and it seems that original data is unreachable. This also happens when the virgin sheet is first converted to text format cells. The (e.g.) 10 and minus and 22 seem to be beyond the grasp of retrievability.
Sorry for the length here, but this greatly condenses the long thread referenced atop. There were fine and sound posts there, but didn't defeat the failure of the examples I just stated.
Imported cell A1 is
=4-6
I cannot figure out how to stop Excel from performing the math. I want to parse out the 4 and 6, but Excel changes it to minus 2.
C1 ="X" & A1
gives X-2
Formatting C1 as text causes it to look like
="X" & A1
(that's actually what's displayed - it even treats the equal sign as text).
This still doesn't give me the 4 and 6 to work with.
Other things I've tried all perform the subtraction. Even MID(A1,1,1) returns "-" - CHEEEEZ!! *** Even if the cells I paste to are formatted as text *** before the paste, there are still cases where Excel "helps" me by making a date or performing subtraction:
Try this test:
format a new sheet's cells A1 to C3 as text;
scrape-copy only the 3 characters 4-6 on this line in this message with Ctl-C;
Ctl-V to A1. Nasty, huh? Are the 4 and the 6 lost forever then?
The Death Example:
Take http://mlb.mlb.com/NASApp/mlb/mlb/standings/index.jsp and scrape and ctl-C the 5 line rectangle, starting your scrape with the left edge of the letter N in New York, through the end of the Toronto line. Paste into a virgin worksheet (general format). You'll see that some cells became dates, and it seems that original data is unreachable. This also happens when the virgin sheet is first converted to text format cells. The (e.g.) 10 and minus and 22 seem to be beyond the grasp of retrievability.
Sorry for the length here, but this greatly condenses the long thread referenced atop. There were fine and sound posts there, but didn't defeat the failure of the examples I just stated.
