Convert tabs to spaces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings:
Using Excel 2003, SP2

Am using the following to create text that will be used in another program.
Column 'C' has extensive indentations that greatly help the readability of
the text. This is lost when the following function is used.

Looking for a way to keep the indenting look, when in Text, by replacing the
indents with spaces. My inititial test indicates that spaces will "stay",
when converting to text - The "space" created by tabs, disappears.


=LEFT(TEXT(D1,".")&REPT(" ",11),11)&LEFT(TEXT(E1,".")&REPT(" ",6),6)&C1
 
If I understand you correctly....
AFAIK, you would need a UDF, to check the .IndentLevel of the cell in
question, then scale that by the number of spaces per indent. Something
like:

=LEFT(TEXT(D1,".")&REPT(" ",11),11)&LEFT(TEXT(E1,".")& REPT("
",GetIndent(C1)*4) & C1

'<In a normal Module>
Public Function GetIndent(WhichCell As Range) As Long
GetIndent = WhichCell.IndentLevel
End Function
'</In a normal Module>

However, if you change the IndentLevel, the formula will not update, as a
..Calculate event does not occur on formatting changes

NickHK
 
Back
Top