Excel indent question 2

[xlhelp]

Does anyone know how to do a hanging indent in a cell?

I already know about typing spaces, but when there is fair amount of text, that becomes cumbersome.

(Using XL2003 but,I am pretty sure that if there is an answer, it would be version independent)

A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

Hi,

In the Format Category (I have stock Tool Bar Icons for Increase & Decrease Indent)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Hi, Skip. Not quite what I am looking for. I have those too.

The result I am looking for is:

[blue]Thinking, or more precisely identification with thinking,
[tab][tab]gives rise to and maintains the ego,which, in our
[tab][tab]Western society in particular, is out of control.
[tab][tab]It believes it is real and tries hard to maintain its
[tab][tab]supremacy. Negative states of mind, such as anger,
[tab][tab]resentment, fear, envy, and jealousy, are products
[tab][tab]of the ego.[/blue]


A man has only two choices: He can be right or he can be happy.

 

[kjv1611]

I always thought that it was just flat impossible, myself. But if it can be done, I'd sure like to know it myself!

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Sorry, I missed the "hanging."

Klunky at best, you could store the first LINE of data separate from the remainder...
[tt]
First LINE in A1
Remainder of LINE in A2, with an INDENT
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Skip,

I believe he means like in a Word document where you can have a hanging indent. The first line is not indented, all subsequent lines in the same paragraph are indented.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Unfortunately, Excel does not have word processor features, just as Word does not have spreadsheet features, althought each can do some of the basic functions that are featured in the other.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

I am quite stubborn, I don't give up that easily. While not quite an unadulterated hanging indent; I did manage to get the following:

[blue]Allan Hills 84001 (commonly abbreviated ALH 84001[1])
is a meteorite found in Allan Hills, Antarctica on
December 27, 1984 by a team of US meteorite hunters
from the ANSMET project. Like other members of the
group of SNCs (shergottite, nakhlite, chassignite),
ALH 84001 is thought to be from Mars. On discovery,
its mass was 1.93 kg. It made its way into headlines
worldwide in 1996 when scientists announced that it
might contain evidence for microscopic fossils of
Martian bacteria.[/blue]

After typing the text and adjusting the column width, I entered a few ALT/ENTER's for each wrap of the text. Then used Edit, Replace command to get the desired result. In the Find box I typed in ALT/0010 and in the Replace box I typed in six spaces; moved the cursor to the beginning by using left arrow key and typing ALT/0010. Essentially replacing alt/ENTER with ALT/Enter and 6 spaces.

A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

Well if you want to go to all that trouble...
[tt]
="now is the time for all"&CHAR(10)&REPT(" ",5)&"good men to come to the"&CHAR(10)&REPT(" ",5)&"aid of their country"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

in fact I'd make the indent variable. Assign a NamedRange like Indent...
[tt]
="now is the time for all"&CHAR(10)&REPT(" ",Indent)&"good men to come to the"&CHAR(10)&REPT(" ",Indent)&"aid of their country"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Ya but....

then I have to use a formula and then copy and paste values and delete one set of values....

......and very hard to do for multiple cells.

Good thought though.

Thanks.

A man has only two choices: He can be right or he can be happy.

 

[kjv1611]

Well, I wonder if you could end up doing it in VBA? I mean, surely, you're wanting to do it on the length of the text in each text box?

So, you'd need to figure the max character count per line, and then make each line break at a point where you don't cut a word in half, and then insert your fancy spacing into those spots.

If nothing else, it'd be a fun challenge to do in VBA, and then someone else might can use it as well.

Who wants to give it a shot?



--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Unless you're using one of the FEW fixed pitch fonts, you'll have a difficult time finding the place to break each line. If you use a Courier font, for instance, you can determine what whole words can fit in the available space.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Hmmm...

What about...

Is there a way to tell where the line break is in the wrapping that goes on, when you have Wrap Text enabled in a cell?


--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

This might work with a FIXED PITCH FONT ONLY...

Function HangingIndent(rng As Range, iInd As Integer)
    Dim iWid As Integer, a, sOUT As String, idx As Integer
    With rng
        iWid = .ColumnWidth
        a = Split(.Value, " ")
        For idx = 0 To UBound(a)
            If Len(sOUT & " " & a(idx)) > iWid Then
                HangingIndent = HangingIndent & sOUT & vbLf
                sOUT = String(iInd, " ") & a(idx)
            Else
                sOUT = sOUT & " " & a(idx)
            End If
        Next
        HangingIndent = HangingIndent & sOUT
    End With
End Function

Use like a spreadsheet function. Paste this code into a MODULE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Well, let me ask it this way:

How does Excel know where to break the line when you have line wrap enabled? Is there any way for us to access that via VBA?



Nonetheless, I've gotta give you a star just on the attempt for that one, Skip! I can't really see much need for it at the moment, but it's sure got me curious!

--

"If to err is human, then I must be some kind of human!" -Me

 

[Gavona]

I agree with

I can't really see much need for it at the moment, but it's sure got me curious!

Started to think about how to address the question:

Column widths are measured as the number of zeros that fit in the column. The zeros are of the Normal style regardless of the font or style of the range. If your Normal style is Arial 10, then the default column width is 8.43, i.e. you can fit 8.43 zeros in the cell.

http://support.microsoft.com/kb/214123

So KJ, you could start by discovering how to find the width of each character in the particular font and go from there.

Maybe though you could approximate via iteration:
assume an average character width (start with a largish value) and apply line feeds accordingly. Check the number of rows. Increment the average width slightly and check if number of rows has changed...

Then you have to think about hyphenation.. the possiblity that the starting text already contains a line feed that you don't want to lose.. and maybe the fact that Excel's word wrapping can go awry with more than 255 characters (maybe different in 2007?). Which all gets me (i) to wonder if you might be better copying your cell content to Word, doing some clever stuff there and then bring the result back into Excel and (ii) to realise that the issue is well beyond my knowledge.

OK Excel-Life balance wins over curiosity.

Gavin

 

[xlhelp]

How does Excel know where to break the line when you have line wrap enabled?

It probably uses Graphics.MeasureString Method

The average character size in Arial font is indeed 8 pixels (with kerning) assuming Excel defaults.

I think I was better off not knowing some this stuff.

A man has only two choices: He can be right or he can be happy.