Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I copy data in Excel and paste it in a text file, I get tabs between cells. That's not good, I want spaces - and I want the columns to be aligned. How can I achieve this without a lot of effort (obviously I don't want to start running regexps by hand etc.)

Using 'Save As' (as in this question) is not a solution for me, I need copying and pasting.

share|improve this question

migrated from stackoverflow.com Dec 19 '12 at 20:00

This question came from our site for professional and enthusiast programmers.

    
i have a possible solution that would involve pasting from one workbook to another workbook (then from the other workbook to wherever you are paste. does that fit with your workflow? –  Pynner Dec 21 '12 at 4:25
    
@Pynner: Well, it fits better than no solution at all, so yes, go ahead. –  einpoklum Dec 21 '12 at 10:36
add comment

2 Answers 2

up vote 2 down vote accepted

This is certainly not simple to explain... but after it is setup it will work with minimal effort. Also this is a slight variation on what @pnuts has outlined

I suggest using a template spreadsheet that you can copy-paste to. the template i have created has 3 tabs that look as follows.

tabs

  • Data tab is where the data is pasted
  • Length is going to do some math to determine the longest column
  • Space insert is going to insert the appropriate number of spaces (edit) you can copy from here and get the correct result

the length tab looks like

length

Row 1 has maximum characters of the column below is and contains the formula

=MAX(A2:A101)+1

+1 creates the delimiter.

Row 2 through to n (which i have extended to 100 for this example) contain a formula to evaluate the length of each string

=LEN('Data tab'!A1)

the space insert tab looks like

space insert

Each cell contains a cell to evaluate the length of itself in comparison with the max value (+ delimiter) and insert an appropriate number of spaces.

='Data tab'!A1&REPT(" ",length!A$1-length!A2)

Note the $ which locks Row 1 if you copy and paste the formula

(edit) you can copy from the space insert tab.

notepad

share|improve this answer
    
Ingeniously contrived! –  einpoklum Dec 23 '12 at 11:15
    
But... why do I need the last sheet, really? I can copy-paste from the third sheet. Also, I don't want to use any long formulae replicated for each column. –  einpoklum Dec 23 '12 at 19:20
    
Quite right.. last tab not required... i had assumed that the cells would insert additional characters. I will update my answer to fix it –  Pynner Dec 26 '12 at 1:44
add comment

Easiest solution would be to concatenate your numeric data into a "commified" string that pastes correctly into a text file. Create the Excel formula for one row and then copy it down for all the rows in the worksheet, and then copy the formula column and paste it in the text file. For example,

Data

Cell A1: 247

Cell B1: 19

Cell C1: 1437

Formula in Cell D1

=A1&","&B1&","&C1

Text-Pastable Result

247,19,1437

There is another approach that does not rely on formulas.

  • First, make sure your data columns are all the same width.
  • Then insert another column between each pair .of data columns, each with a width of 2.
  • Enter a single comma in each of the inserted columns, copying it down the length of the data.
  • Finally, save the worksheet as a Formatted Text (Space delimited) (*.prn) file.

A benefit of this alternative is that it preserve the formatting of your data. If you want to comma-format the data, or just show one decimal place, your formatting will be preserved in the prn file.

share|improve this answer
    
Can you get this to be (i) space delimited and (ii) aligned, as OP request and (I'd guess) (iii) with spaces already in some cells (ie text" "words)? –  pnuts Dec 20 '12 at 10:19
    
eg with {=MAX(LEN(A1:C1))+1} in say 'E1' and =REPT(" ",E1-LEN(A1))&A1&REPT(" ",E1-LEN(B1))&B1&REPT(" ",E1-LEN(C1))&C1 in 'D1' (assuming non-proportional font in text document). -Save as etc may be simpler! –  pnuts Dec 20 '12 at 10:34
1  
pnuts suggested mprovement is exactly right. I've updated my answer with another alternative that uses Save As. –  chuff Dec 20 '12 at 19:48
    
So, like I said in the question, saving-as is not an option. Will the REPT solution work with copy-pasting? Also, I'm not accepting yet as I would like something less cumbersome. –  einpoklum Dec 21 '12 at 10:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.