Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text to columns by format type???
thanks, that worked perfect...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to apply Macro on a same type of format but columns no. r chan | New Users to Excel | |||
Text to columns with date format does not work :( | Excel Worksheet Functions | |||
how to convert date type to text type | Excel Discussion (Misc queries) | |||
Changing text to number format using type casting | Excel Programming | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) |