Excel – How to paste Excel data as fixed-width text

fixed widthmicrosoft exceltext editing

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.

Best Answer

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

Related Question