Text to columns by format type???
 

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

Text to columns by format type???
 

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

--
Regards,
Tom Ogilvy



"Matt" wrote in message
oups.com...
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

Text to columns by format type???
 

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/c...ecticides.html

thanks

Text to columns by format type???
 

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


--
Regards,
Tom Ogilvy

"Matt" wrote in message
oups.com...
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/c...ecticides.html

thanks

Text to columns by format type???
 

thanks, that worked perfect...