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.

I have an Excel file with a column of codes which indicate parent-child relationships with some kind of formatting:

Codes

The indent is controlled with "Indent Level" option on "Left (Indent)". Copying and pasting to a text editor doesn't keep spacing.

I'd like to be able to copy and paste spacing to a text editor. Can I somehow make the formatting explicit - add a column that indicates the indent level or anything similar to that?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

I don't think you can do this with the built-in features of Excel, however you can create an Excel macro to do this:

Sub CopyCellValuesWithIndent()

    Dim rSelection As Range, c As Range
    Dim lIndent As Long, i As Long
    Dim sCopyString As String
    Dim DataObj As Object

    'LATE BIND THE "MSForms.DataObject" LIBRARY TO USE THE CLIPBOARD
    Set DataObj = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    Set rSelection = Selection

    For Each c In rSelection.Cells

        lIndent = c.IndentLevel

        'ADD INDENT
        If lIndent > 0 Then
            For i = 1 To lIndent

                sCopyString = sCopyString & vbTab

            Next i
        End If

        sCopyString = sCopyString & c.Value & vbCrLf

    Next c

    sCopyString = Left(sCopyString, InStrRev(sCopyString, vbCrLf))

    DataObj.SetText sCopyString
    DataObj.PutInClipboard

End Sub

Open your VBA Editor (Alt+F11), create a new module and paste in the code above. After which, select the cells containing the values you want to copy (with indent) and after running this macro simply use the paste option in your text editor to retain the indent levels.

share|improve this answer

If you prefer to avoid VBA you could try this. This is my preferred method - I simply set column widths to be very narrow (eg 10px).

nested rows

You can switch off gridlines, or set borders to make it look nicer.

nested rows without gridlines

If your data includes numbers (as your sample data does), you'll need to convert them all to text by adding a ' (apostrophe) to signal text data - otherwise the full content will not appear and you'll just get #.

If you do this you'll get these green tags saying you have an error. Select all, right-click and say ignore errors.

ignore errors

When you copy to your text editor, the indents will be there as tabs.

tabs in notepad

share|improve this answer
    
+1 for the non-VBA solution. Note that it is not necessary to use an ' (apostrophe) to store numbers as text (thus retaining leading zeros) if you simply change the cell number format to Text prior to entering the value. –  Taliesin Apr 5 '13 at 17:47
    
I tried that but it doesn't work for me. I'm on Excel 2010. If you find you don't need to do that then great :) one less step! –  Andi Mohr Apr 5 '13 at 17:51

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.