Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 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
up vote 7 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.


  • 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


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


+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.


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
Excellent answer, thank you very much. Personally some of my columns where coming out very long, so I added a page with "=LEFT( 'space insert'!A1, $K$2) & IF(Length!A2 > $K$2, "...|"," |")" where $K$2 is set to an appropriate value (the max of the most important column). This works well for descriptions etc, next step wrapping :p – chrispepper1989 Sep 21 '15 at 9:29

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,


Cell A1: 247

Cell B1: 19

Cell C1: 1437

Formula in Cell D1


Text-Pastable Result


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
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

You can copy your data in Word and then use the find and replace (ctrl+h) to replace the tabs with spaces.

  1. Open and paste data in Word
  2. Press Crtl+h
  3. More >>
  4. Special
  5. White Space
  6. Put a space in the replace with:
  7. Replace all
share|improve this answer
That's won't do what I asked for, sorry - the column widths will differ. – einpoklum Apr 15 '15 at 21:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .