Data Import & Export

How to Move Excel Cells into Columns Based on Indentation

Ayan Masood Oct 10, 2026 871 views

Question details

The user needs to move hierarchical data from a single column into multiple separate columns based strictly on the alignment indentation level of each cell.

How to Move Excel Cells into Columns Based on Indentation

Product: Excel
Device & OS: not provided
Scenario: Restructuring imported hierarchical data where parent-child relationships are represented by cell indentation rather than spacing or delimiters.
Observed behavior: The hierarchical data is contained within a single column using formatting (alignment indentation) instead of space characters, making standard delimiter-based extraction impossible.

Before you start

Before restructuring your data, double-click a few cells to verify that the visual gap is actually caused by alignment indentation and not by typed space characters. Create a copy of the original column to preserve the initial hierarchy.

Solution 1Recommended

Use Find and Replace Formatting to Extract Indentation Levels

Identify specific indentation levels using Excel's Find format options, assign them a unique fill color, and use filtering to separate the data.

Since standard formulas and Text to Columns cannot read cell formatting directly, the most straightforward non-VBA method is to search for the specific indentation formatting and tag it with a background color or helper value.

Open Find and Replace

Select the column containing your indented data, press Ctrl + H to open the Find and Replace dialog, and click the 'Options' button to expand advanced settings.

Set the Find Format

Click the 'Format...' button next to the 'Find what' field. Navigate to the 'Alignment' tab, set the 'Indent' value to the specific level you want to extract (e.g., 1), and click OK.

Apply a Replacement Format

Leave the 'Replace with' text box empty, but click its adjacent 'Format...' button. Go to the 'Fill' tab, select a bright background color, and click OK. Click 'Replace All' to color-code all cells with that specific indent level.

Filter and Move Data

Apply a filter to your header row (Data > Filter). Filter the column by the newly applied cell color, select the visible cells, copy them, and paste them into the designated target column.

Repeat for Other Levels

Clear the filter and repeat the Find and Replace process for subsequent indentation levels (e.g., Indent 2, Indent 3) using different colors, until all hierarchy levels are separated into columns.

Use Find and Replace Formatting to Extract Indentation Levels

Alternative to Copy-Pasting: Instead of filtering and copying, you can use a VBA function like `Range.IndentLevel` to create a custom formula that automatically reads the indent level into a helper column.

Solution 2

Restructure with Microsoft 365 Formulas

If you have added a helper column indicating the level of hierarchy, use lookup formulas to populate the columns dynamically.

Organize Data with WPS Spreadsheet

Easily Manage Formatted Data with WPS Office

WPS Spreadsheet provides powerful formatting search tools and robust formulas, enabling you to quickly identify cell indentation and restructure complex hierarchical data.

1. Open Your File: Launch WPS Spreadsheet and open the file containing your indented column data.
2. Access Find Formatting: Press Ctrl + H, click 'Options', and use the 'Format' button to specify the indentation level in the 'Find what' field.
3. Tag with Color: Set a distinct background fill color in the 'Replace with' format settings and click 'Replace All'.
4. Filter and Relocate: Use the Data filter to isolate the colored cells and move them to their corresponding new column.

Advanced Find and Replace tools to detect and modify cell alignment formatsFull compatibility with Microsoft Excel (.xlsx) formulas and formattingIntuitive interface for filtering by color and managing large datasetsFree to download and highly lightweight for efficient processing

microsoft office alternative - wps office

Frequently Asked Questions

Can I use Text to Columns to split indented cells?

No, the Text to Columns feature relies on physical characters such as commas, tabs, or spaces to act as delimiters. It cannot read cell formatting properties like alignment indentation.

How can I check if my data uses spaces or alignment indentation?

Double-click the cell to enter Edit Mode. If the cursor is positioned directly next to the first letter and you cannot use the arrow keys to move left into the blank space, the visual gap is created by alignment indentation.

Is there an Excel formula to detect the indent level of a cell?

There is no standard built-in formula (like =INDENTLEVEL) that reads formatting. However, you can use VBA to create a custom User Defined Function, or use the older Excel 4.0 macro function GET.CELL to extract formatting information into a helper column.

Excel & Data Problems