Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2006
    Posts
    16

    Unanswered: Excel exporting question

    Help please. I am using excel 2003. I have cells with the following data

    Y N Y N Y Y NA Y N N N NA NA N Y NA Y

    Each y, n or na is in a separate cell. There are several rows like this although the y, n or na will be in different places in each row. I would like to create a text file (.txt) that looks like this
    Y N Y N Y Y NAY N N N NANAN Y NAY

    Must have only 1 space between the data and no spaces if the data is 'na'.

    Every I try to copy it into the .txt file in notepad it creates extra tabs/spaces between the data even though I resized the cells in excel to autofit.

    Thanks
    KAT

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Resizing the cells will not have any effect.

    If you save as text file, it will always give a standard space (or tab) as the seprator, it will not change based on cell value. I suspect that the only way to do this will be to use VBA to create and array and then export the array in VBA.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2006
    Posts
    16
    Hi Shades. VBA? Is that Visual Basic Access? I don't know how this works with Excel. Can you be more specific?

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Visual Basic (for) Applications, for use with Excel, Access, Word, PowerPoint
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    =a2 & if(a2="NA",""," ") & a3 & if(a3="NA",""," ") & A4 & if(a4="NA",""," ") ......

    Then copy the above line a note pad file
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Oct 2006
    Posts
    16
    Okay, thanks Myle. I'm going to try this.

  7. #7
    Join Date
    Oct 2006
    Posts
    16
    Thanks. Yes, its working (except I'm using a1, b1, c1 etc), but I should have mentioned that there is another cell I have to concatenate at the beginning. I was thinking I didn't need to mention it because I figure if I got the logic to the other part then concatenating at the front would be no problem. Well it is a problem!

    I need to concatentate the contents from another cell (at beginning of row)with no spaces before the 'y', 'n' or 'na'. The cell to be added in front is always three characters (numbers and letters), but with varied values. Everytime I try =x1 & a1 or =x1 & if(x1<>" ",""," ") I get a space before 'y', 'n' or 'na'.

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Your last part is has "if no space, then add a space.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Oct 2006
    Posts
    16
    Shades, I don't really understand what you are saying. What should the syntax be for concatenating in front with no space?

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    Use the + to add to cell together 1+1 = 2
    use the & to join to call together 1&1 = 11

    If can have if in side them

    =if(if(bla,bla,bla),if(bla,bla,bla),if(bla,bla,bla ))

    That how I get the or and and

    never had much luck with the #or# and #and#

    Think i'm just old old school (lotus 123 v 1) is where I started
    even still got the 51/4 disks it came on
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •