How to paste Excel data as fixed-width text?

Question

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.

Answer 1

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

=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

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.

Answer 2

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.

Answer 3

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

Answer 4

Using the open-source software R and R Studio, one can use the datapasta R package to paste from Excel into a data frame. Then using the knitr R package kable function a la knitr::kable(pasted_dataframe), the provided table pastes neatly in Notepad or the like.

This is a very fast process after one installs the software.