Solved

# Indenting in Excel VBA

Posted on 2013-06-11
483 Views
Hi all,

I have the following data:

2
2.1
2.1.1
2.1.2
2.2
2.2.1
2.2.2
2.2.3
2.3
2.3.1
2.4
2.5
2.6
2.6.1
2.6.1.1
2.6.1.2
2.6.2
3
3.1
3.1.1
3.1.2

This data is the first column of a list of parent and child items, so item 3.1 belongs to 3 and item 3.1.1 belongs to item 3.1 and so on.

What I would like to do is the following, showing each parent in a separate column:

2
2.1            2
2.1.1            2.1
2.1.2            2.1
2.2            2
2.2.1            2.2
2.2.2            2.2
2.2.3            2.2
2.3            2
2.3.1            2.3
2.4            2
2.5            2
2.6            2
2.6.1            2.6
2.6.1.1            2.6.1
2.6.1.2            2.6.1
2.6.2            2.6
3
3.1            3
3.1.1            3.1
3.1.2            3.1
etc...

Has anyone got any clever ideas of how to achieve this is Excel VBA?

0
Question by:FMabey

LVL 24

Expert Comment

ID: 39237605
you could use formula:

=LEFT(A2,LEN(A2)-2)
ee.xlsx
0

LVL 13

Accepted Solution

Shanan212 earned 500 total points
ID: 39237700
``````Sub sample()

Dim l As Long, i As Long

i = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
l = Len(Range("A2").Value)

For i = 2 To irow

Range("B" & i) = Left(Range("A" & i).Value, l - 2)

Next i

End Sub
``````

Assumption is that all your data is on column A and output will be in column B
0

## Featured Post

### Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …