Text to columns by format type???

[Matt]

I am working a chemical list in which I am trying to hierarchically
classify specific chemicals. I copied a list of these classification
terms. I cannot figure a way via text to columns or import data to get
the data to fall into specific columns. When you copy the data into
excel all of the data falls into column one, but the hierarchy of the
data stays intact by increasing indent values. For instance, the
mother group "Tree" is left indent 1; the "Oak Tree" is left indent 2;
and the "Red Oak Tree" and "Blue Oak Tree" are left indent 3. Is there
an easy way to pull these values into separate columns. I tried
writing a short macro, but have yet to come up with anything that
works. Any help would be great, Thanks, Matt

 

[Tom Ogilvy]

Make a copy of your data, select it, then run this macro.

Sub fixdata()
Dim cell as Range
Dim i as Long
for each cell in selection
i = len(cell) - len(ltrim(cell))
if i <> 0 then
cell.offset(0,i) = Trim(cell.Value)
cell.clearcontents
end if
Next
End Sub

 

[Matt]

Thanks for the feedback, but I think I did not explain it correctly.

From my understanding the macro above would 'Trim' the empty spaces.

But the data I have does not have excess spaces. The data is all in
one column but the hierarchical structure is shown by increasing indent
format. As in, when I click on format cells and go into alignment the
first order class has a left alignment ident value of 1 and the second
order data has left alignment indent value of 2...

here is the URL I am pulling data from and trying to capture the
hierarchy.
http://www.alanwood.net/pesticides/class_insecticides.html

thanks

 

[Tom Ogilvy]

This is a good start. For me, a few values were indented by spaces as well
as having an indent.

Sub fixdata()
Dim cell As Range
Dim i As Long
For Each cell In Selection
If cell.IndentLevel > 0 Then
cell.Resize(1, cell.IndentLevel).Insert xlShiftToRight
cell.IndentLevel = 0
End If
Next
End Sub

 

[Matt]

thanks, that worked perfect...