I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.

1. Select the column that contains the indented text.
2. Click on the Formulas tab in the ribbon.
3. Click on the "Insert Function" button.
4. In the search box, type "LEN" and select the LEN function from the list.
5. In the "Text" box, select the cell that contains the indented text.
6. Click on the "OK" button.
7. The LEN function will return the total number of characters in the selected cell.
8. Now, we need to subtract the number of characters in the cell without the indent. To do this, we'll use the SUBSTITUTE function.
9. Click on the "Insert Function" button again.
10. In the search box, type "SUBSTITUTE" and select the SUBSTITUTE function from the list.
11. In the "Text" box, select the cell that contains the indented text.
12. In the "Old_text" box, type a single space (or whatever character is used for the indent).
13. In the "New_text" box, leave it blank.
14. In the "Instance_num" box, type "1".
15. Click on the "OK" button.
16. The SUBSTITUTE function will remove the first indent from the selected cell.
17. Subtract the result of the SUBSTITUTE function from the result of the LEN function.
18. The final result will be the number of indents in the selected cell.

Alternatively, you can replace each indent with another character that you can count via formula. To do this, you can use the SUBSTITUTE function again. Here's how:

• Select the column that contains the indented text.
• Click on the Home tab in the ribbon.
• . Click on the "Find & Select" button.
• Click on "Replace".
• In the "Find what" box, type a single space (or whatever character is used for the indent).
• In the "Replace with" box, type a character that you can count via formula (e.g. "|").
• Click on the "Replace All" button.
• All the indents will be replaced with the character you specified.
• Now, you can count the number of characters in each cell using the LEN function and divide it by the number of characters in the replacement character (e.g. "|") to get the number of indents.

This is a great question!

Say we have a cell with indented contents and want to know the indent level.
Consider the following one-line UDF:

Function indenture(r As Range) As Integer
indenture = r.IndentLevel
End Function

If A1 has been indented 3 times,
=indenture(A1) will return a 3
--
Gary''s Student - gsnu200825


"MParham" wrote:

I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.

I haven't worked with Hyperion, but am surprised that a non-excel program
provides text indents that Excel recognizes as such.
I am not aware of any Excel function or formula that returns an indent level.
The vba phrase you need is "IndentLevel";
it is a property of the range object. (and Style object).
It can be used as... Cells(2, 4).IndentLevel

A vba worksheet function you can call from the cell immediately
to the right of the indented cell is...
'--
Function HowMany() As Double
On Error GoTo NotEnough
Application.Volatile
HowMany = Application.Caller.Offset(0, -1).IndentLevel
Exit Function
NotEnough:
HowMany = 9999
End Function
--
Note - changing the indent level requires a sheet calculation to update the function.
Jim Cone
Portland, Oregon USA



"MParham"

wrote in message
I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.

I appreciate your help on that one. It worked out beautifully. I am still
learning the Macros and Visual Basic and I am getting better. I do have
another question related to this one. When i created my Macros I did not have
to do anything special for them to affect any active journal. The Function
seems only to affect the journal I created it in, even though it is the one
that automatically loads everytime I boot Excel with all my Macros. I can't
seem to make the Function globally available. How do i make it so i can use
this functionon in any journal I am working on?

"Gary''s Student" wrote:

This is a great question!

Say we have a cell with indented contents and want to know the indent level.
Consider the following one-line UDF:

Function indenture(r As Range) As Integer
indenture = r.IndentLevel
End Function

If A1 has been indented 3 times,
=indenture(A1) will return a 3
--
Gary''s Student - gsnu200825


"MParham" wrote:

I have a Hyperion report that gives me the Chart of Accounts in a way that
each level is indented when viewed in Excel. There are no spaces to count, it
is all done with indents. I need to convert the file dividing them out into
Level groups so I need to know how many times the item was indented so I can
write a macro or formula to move it to the proper level. When an item is
indented, how do you get the computer to calculate the number of indents? If
you can't do that, can I replace each indent with another character I can
count via formula?

An example of what I have is:

Account 1
Account 1a
Account 1b
Account 1b1
Account 2

That is just something to show how my report comes in. I used spaces in the
above example but the report does not, it uses indents and I need to be able
to calculate how many indents on each Account so I can use another formula to
automatically place it in the right place of another form.