Welcome to Netrider ... Connecting Riders!

Interested in talking motorbikes with a terrific community of riders?
Signup (it's quick and free) to join the discussions and access the full suite of tools and information that Netrider has to offer.

MS Excel Nerd Help please.

Discussion in 'The Pub' at netrider.net.au started by doonx, Feb 23, 2006.

  1. I have searched in the online help, it's up to Microflop's normal standard.

    I am extracting data from a system into a CSV file. I open that CSV in Excel and copy the data I want.

    I then Paste Special (Values) into another Excel spreadsheet in order to apply my preferred formatting. Some cells have A LOT more data in the formula bar than they display on-screen in the cell

    eg : This is in the FORMULA BAR and is the complete data :

    10/02/2006 11:48:44 AM infsys Various requirements are emerging for TP based unit data o be 're-assembled' into logical Semesters, i.e. 1 and 2. Bodies requiring this are DIMIA, (ESOS), Centrelinkas well as possibly some internal systems. We need to deal with this holistically and consistently. The solution, (SPAD), needs to address the business rules, and maybe one mapping size will not fit all, and take into account the new flexible academic calendar for delivery on the cards for 2007. Architecturally the solution should assume the following: 1 - ASCOL is currently supporting the transactional level, lowest level of any data required for other entities, but the grouping logic should not be implemented here, instead it should reside in the HORUS tier 2/3 layer. 2 - Any logical mapping should be built as a re-usable component, (set of data), that can be reffered to by numerous ETL functions or applications. 3 - Long term implementation as a Web Service may be beneficial for ad-hoc verification or grouping. 4 - Assume that the data structure may require many instances of mapping groups to satisfy possible disparate interpretations of Semesters. 0_Due_2006/31/03 Logical Grouping of TP's to Semester View 10/02/2006 11:49:50 AM infsys MH - This has been assigned to GLS to build SPAD.

    But this is all that the cell displays :

    http://trapdoor.darb.net/albums/General-Forum-Images/excel_cell.jpg



    resizing the cell doesn't help. The cell formatting is:

    Column Width = 38 (needs to be to print onto a single page)
    Number = Text
    Align = General/Top
    Wrap
    Font MS Sans Serifs
    Size 8

    Even when I select the entire sheet then double click on a column or row border in order to resize to fit all text in, it doesn't change.

    Any ideas on how to display and print ALL the data ?

    Thanks
     
     Top
  2. try using a standard xls format, i know csv is very limited with some things, this may be one of those things...
     
     Top
  3. if you get stuck on this, a good quick (free if done right) way to get an answer is in http://experts-exchange.com/

    I don't use Excel so I can't help you
     
     Top
  4. nope, i was wrong, same thing in xls too....

    ummm... looks like there is some kind of character limit on a single cell :? never encountered it before, you've got me beat..... good luck!! :p
     
     Top
  5. Am I assuming correct that this information is just that and only needs to be displayed as text? Have you thought about adding the text in a text box instead of into the cell? You can then have the text box whatever size you like and position it wherever you like on the worksheet.

    Greg
     
     Top
  6. wont work oz, I need to paste hundreds of lines from the CSV into the XLS. I will do the extract weekly, so the data is always changing.

    thanks anyway
     
     Top
  7. Excel doesnt like it when you use more than 1000 characters in a single cell so the only way to get round this is to manually force it to create new lines in the text. You can do this by pressing alt+Enter whilst in the formula (ie the body of the text). Obviously if you're doing this on a large scale it might not be practical, but this is the only way I know around it.
     
     Top
  8. merged cells? might give u more space to type?
     
     Top
  9. does it have to go into Excel?
     
     Top
  10. This help at all?

    Doesn't sound like there is anyway to display more than 1024 character per cell so you might need to expand into another cell and see how it goes when you line it up.
     
     Top
  11. that is a bit harsh, doonx doesn't need a sex change, expert or not

    :LOL: :LOL:

    I always wondered if they noticed that when they went with the address
     
     Top
  12. Thats not correct matt as I stated in my post, you can force excel to view more by creating new lines. Normally if you have "wrap text" ticked it will automatically do this but once you get over a 1000 characters it stops wrapping, the only way to counter this is to wrap the remaining text yourself using alt+Enter (which is the same as pressing enter in 'ms word' to make a new line). As for merging into another cell, this makes no difference either, a merged cell becomes 1 cell as far as excel is concerned and the same issue will occur.
     
     Top
  13. 32,767 is the correct size because it is the max capacity fo the Data Type Char. You cannot store beyond this.
    Alternate Solutions:
    1. Open the CSV Direct using excel instead of copy paste to Excel. This will avoid misrepresentation of any characters as Hyperlink. Thus you will not get addresses instead of text.
    2. Open the File in Textpad, save as txt and then save in csv without changing any formats ie. Tabs.
    3. Write a quick macro (heaps available in the google for excel) on the excel to remove all Hyperlinks from cell values.
    4. Send me a sample files csv and excel and I hope I can fix it for you. rajiv.tarafdar@gmail.com :)
     
     Top
  14. Hey russ - your'e still around !

    SOLVED !!!!

    Do exactly as I stated in first post, then copy entire spreadsheet and drop it into a Word Doc. Entire data is displayed and printed in table cell of Word. As I am needing to produce a report, this delivers it perfectly !

    Thanks for you assistance

    Cheers
    Doonks
     
     Top
  15. Doonks my man, u know u wasted peoples time by posting this as an excel issue when all u were talking about was copying and pasting text. Why you needed help on that is beyond me. You didnt need an excel wiz with help on that.

    I and I think a few others assumed that you actually needed it in excel for some reason. And dont worry about thanking everyone for putting forward suggestions, we dont have anything better to do with our time.

    Good thing u thanked russ for his humour though (and it was funny, never noticed that before)
     
     Top
  16. oi you pommygit - I asked for help cause I needed it. I'm not an IT geek which is why I asked for nerd help.

    If you care to read my first post, you'll also see that I thanked all in advance !

    and another thing, I didn't hold a gun to anyone's head and insist they waste time on this. Next time, maybe you shouldn't bother helping anyone at all, and Karma will deliver you the same courtesy.

    geez :roll:
     
     Top
  17. ah come on.. a little thanks at the bottom of ur original post isnt a proper thanks in advance mr scrooge

    and i appreciate ur not an IT nerd but u put this out as an excel issue.. naturely most people who responded tried to figure out how to solve ur problem IN EXCEL.. and in the end u just pasted it into word. Sorry i shouldnt give u hard time about that but i am a git after all... whch u can see by my 3 warnings and great friendships ive accumulated in the short period ive been posting but come on mate.. give a little love to all those who put some effort into helping u... :-({|=

    there wont be any cadburys roses waiting for u when u get home.. let me tell u
     
     Top
  18. Funny thing is when I was testing for it, I wondered why not highlight the stuff in the formula bar and use the print selection option.

    I didn't think that's what you wanted so I didn't say anything. I did wonder about dumping into a Table on Word as well but again thought you really needed to use Excel.

    I find while Excel is great for most things, when it comes to importing stuff it does play up.

    Anyway looks like your set. I found all the ideas great to read whether they worked or not doesn't matter to me.


    Regards
     
     Top
  19. I stand corrected in regards to the 1024 limit, but then I only did 5 minutes of search on google to come up with that.

    Never mentioned merging cells for the exact reason you state, I was referring to breaking the text across two or more cells.

    If you are in IT then you know that there person asking the question or highlighting the problem (i.e. I can't get my emails!!! Is the computer turned on? Errr....*windows starting sounds*......hey look it fixed itself I don't need you anymore!!!) never give you the full story but to me I got the feeling it as just a layout thing hence suggesting the breaking the text across more cells.

    I don't think I wasted my time doing a quick bit a research for him, I learnt something new. Perhaps Doonks didn't use the knowledge you provided but I've picked up an extra trick and I'm sure there are a few others that will benefit from the knowledge. So thanks!

    Surely by now you know IT support is a thankless task, you can't expect a big pat on the back or a pocket full of cash everytime.
     
     Top
  20. yo!

    yeh I'm still here, making smartass comments then running away...

    still haven't made it to a netrider event, though I swear i will one day. I take it you're no longer near swinnny? I haven't seen your bike for ages
     
     Top