Anyone good with Excel?

Discussion in 'Off Topic Chat' started by xjrrrdx, Jan 20, 2009.

  1. xjrrrdx

    xjrrrdx TPF Noob!

    Joined:
    Oct 15, 2005
    Messages:
    93
    Likes Received:
    0
    Location:
    Denver / Germany
    Ill ask if anyone is good with excel because I am having the hardest time with a project at work.

    Ill post my issue if anyone is willing ;) Ill save the long post until I can get some assistance.
     
  2. Eldrich

    Eldrich TPF Noob!

    Joined:
    Dec 16, 2008
    Messages:
    314
    Likes Received:
    0
    Location:
    Central PA
    Can others edit my Photos:
    Photos OK to edit
    Hard to know if 'good' is good enough without knowing the issue.
     
  3. Village Idiot

    Village Idiot No longer a newbie, moving up!

    Joined:
    Mar 20, 2008
    Messages:
    7,274
    Likes Received:
    406
    Location:
    Shepherdsturd, WV / Almost, MD
    Can others edit my Photos:
    Photos NOT OK to edit
    I spend my days staring blankly at lines of code for Access. Excel shouldn't be too difficult.
     
  4. xjrrrdx

    xjrrrdx TPF Noob!

    Joined:
    Oct 15, 2005
    Messages:
    93
    Likes Received:
    0
    Location:
    Denver / Germany
    Okay, here is the issue, lets see if I can translate it to where people can understand my brain lol.

    We are working with google earth to create various placemarks via a KML file. We use a KML builder which bases everything off Excel workbooks.

    I have 7000 files that need to be parsed sorted and broken into the correct formats for the builder.

    What I do is copy the file DIR in dos into a text file, import the text into excel.

    I use the built in excel tool to breakup my file name which looks like this (into seperate cells):

    YYYYMMDD_1200z_000000N_0000000E_Name

    I bring them in as "general" but where the date is it cannot format it because it cant read it as numbers and I just get #### marks.

    Thats the main issue, I use a lot of formulas to get my cells to looks like I want. But the builder that chooses all the cells I want seems to view the formulas only and not the output. I am sure its a simple fix but I cant fix it at all!

    I wish I could download stuff to my machine but due to the classification of the material there is no internet access.

    I hope I made it clearish. Its been driving me nuts trying to figure this damn thing out!! :grumpy:
     
  5. Village Idiot

    Village Idiot No longer a newbie, moving up!

    Joined:
    Mar 20, 2008
    Messages:
    7,274
    Likes Received:
    406
    Location:
    Shepherdsturd, WV / Almost, MD
    Can others edit my Photos:
    Photos NOT OK to edit
    I imported it just fine using the exact line you provide.

    How exactly do you import? I opened the text file and when the text import wizard came up, I changed the first column to date: ymd and it imported it as it should have been.
     
  6. xjrrrdx

    xjrrrdx TPF Noob!

    Joined:
    Oct 15, 2005
    Messages:
    93
    Likes Received:
    0
    Location:
    Denver / Germany
    I do it just by using the wizard. I do it by parsing using the slider to cut off where I want the date to end and do YMD but when it imports it, it does not like it. If I go to format the cell it wont let me change any of the data it just stays in the 200800101 format, the only way to get it the format I like is the =A1&"/"&B1&"/"&C1" formula but if I use it then only the formula is seen and of course pops an error up as ivalid entry.

    It will split up properly cell by cell like I want but its almost like the data in the cell is just staying text. Not converting like I check the box to do.

    Its Office 2007 and there are so many issues with things but its so odd to get this not to work like it should.

    I am sure its my issue, but maybe I can pawn it off on the machine lol.
     
  7. Village Idiot

    Village Idiot No longer a newbie, moving up!

    Joined:
    Mar 20, 2008
    Messages:
    7,274
    Likes Received:
    406
    Location:
    Shepherdsturd, WV / Almost, MD
    Can others edit my Photos:
    Photos NOT OK to edit
    Using 2003 at work.

    Is there a way to delimit fields by choosing the delimiter? In '03 you can choose what your delimiter is and I used the under score like you have.

    Another thing is if it is the delimiter that's giving you problems, you can replace all the _'s with spaces using excel and bat files. It's a pain, but if that's what's causing the error.

    Do you currently have any formulas in place in the date column that could be causing a conflict with what you're trying to import?
     
  8. xjrrrdx

    xjrrrdx TPF Noob!

    Joined:
    Oct 15, 2005
    Messages:
    93
    Likes Received:
    0
    Location:
    Denver / Germany
    No the cells are blank. I used delimiters before but it just keeps them as "general" data. I will give it another go tomorrow.

    I am getting it done, but I am just using hidden cells to hold my spare data to get what I want.
     
  9. bikefreax

    bikefreax TPF Noob!

    Joined:
    Jan 6, 2008
    Messages:
    379
    Likes Received:
    1
    Location:
    Columbia, MO
    Can others edit my Photos:
    Photos OK to edit
    Sounds to me like you need to format those specifiec cells. Highlight it and right click and go to format. Then pick either the date or number and make sure it is set up right. Hope this helps.
     
  10. Aggressor

    Aggressor TPF Noob!

    Joined:
    Feb 13, 2008
    Messages:
    183
    Likes Received:
    0
    Location:
    Vancouver, BC
    Can others edit my Photos:
    Photos OK to edit
    Sometimes formatting simply by number won't work. You need to write a macro that will put an apostrophe (') in front of each value. The first apostrophe will not be visible and serves only to tell Excel that the following text is... well, text.
     
  11. xjrrrdx

    xjrrrdx TPF Noob!

    Joined:
    Oct 15, 2005
    Messages:
    93
    Likes Received:
    0
    Location:
    Denver / Germany
    Thanks for all the help it seemed the problem was Excel didnt like it when I used a Fixed Length Delimiter, but id I did a delimiter that seperated by _ then it worked like a charm!
     

Share This Page