16

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.

2
  • 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
14

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

5
  • 1
    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
  • next step would be proper alignment of numericals – vlad_tepesch Jan 8 '19 at 14:13
3

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.

2
  • 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
0

You can copy your data in Word and then use Find and Replace (Ctrl+H) to replace the tabs with spaces.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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