Copy and paste from Subset Editor to Excel

[Steve Rowe]

I often copy and paste elements from the subset editor as a quick and easy way of building reports and so forth. What I'd like to know is if there is an option to "stop excel thinking that things may just possbly represent a date so I'll intefere with the format in the cell, oh and I think I'll change the underlyng value as well"?

For example there's an element in the dimension "6-1900", when I paste this into Excel the format gets changes so the cell value is the 01/06/1900 and when I put the cell format to text I get 153.

It's a real pain in the butt! There's various cumbersome workarounds in TM1 that I can do to get the information into Excel but anyone know of a way of stopping it happening in the first place?
Cheers,

[Eric]

I think formatting the column/row/cell to text before your paste should do it.

[paulsimon]

Steve

I long ago got in to the habit of always giving element names an alphabetic prefix, then all these problems go away, as well as the nastier problems that you get when eg 100001 in both the Account and Cost Centre dimensions. If you have A_100001 and C_100001 then there are no problems and you can also tell easily which dimension an element belongs to.

Eric's suggest works up to a point, but I have still had some odd results even when the column is formatted as text. Even standard Excel functions like VLOOKUP can give odd results.

Regards


Paul

[Alan Kirk]

I often copy and paste elements from the subset editor as a quick and easy way of building reports and so forth. What I'd like to know is if there is an option to "stop excel thinking that things may just possbly represent a date so I'll intefere with the format in the cell, oh and I think I'll change the underlyng value as well"?

For example there's an element in the dimension "6-1900", when I paste this into Excel the format gets changes so the cell value is the 01/06/1900 and when I put the cell format to text I get 153.

Add to that when you paste something with commas (from an external data source) into Excel, and Excel spreads it across multiple cells assuming that you want it to be comma delimited data. (Unless you go into cell edit mode first, and paste it in that way.)

Unfortunately there are some things that Excel is bound and determined to "help" you with (with no way to turn them off), and you describe a couple of them.

I concur with Eric's suggestion.

The problems that Paul described are ones that I've only had when I've formatted the cells as text after the fact. In that case, I've found that formulas will still sometimes see the cell entries as numeric values rather than strings. Whacking a single quote mark (or other text identifier) in front of the cell entry then makes the formulas treat it as text, but it's the only thing that does. I don't recall ever having had problems when the cell was pre-formatted as text, though. (Doesn't mean that it's never happened, just that I don't recall it ever happening. And I'm most assiduous about keeping an "Oh yes, some day you will pay for this" grudge list of all the software that I use.)

Oh, and one should ALWAYS format the columns beyond the values as text when copying from the transaction log dialog. Especially when you have element names with leading zeroes, and you want to do a DBRW on each row to check the current value.

[Steve Vincent]

Excel is crap, live with it

Long ago i got bored of being annoyed by the way it tries to help you by converting things that have no need to be converted. Granted, sometimes its really useful but just like TM1 it'd be nice to have the option to turn it on and off, but we don't so you have to live with the consequences. Just like opening a CSV direct in xHell and then loosing all the leading zeroes from fields such as clock numbers...!

[Eric]

Even standard Excel functions like VLOOKUP can give odd results.

If you ever get odd results in VLOOKUP() or any other formulas. nest TEXT(A1,"@") and it will convert the data to text or VALUE(A1) will convert it to a number.

Eric = Excel junkie (I know it explains a lot)

ExHell

Never heard that one... Nice

[paulsimon]

Eric

Thanks. I tend to use =A1&"" which also does a text conversion. The point is that I don't want to have to start fiddling around with formulas, or formatting things as text, which then means that I can't enter a formula without Excel treating it as text. If I prefix all my element names with an alpha character I just don't get these problems (except when I go to a client's site where someone has got there before me, and has used numerics for element names).

Regards


Paul Simon