Create hanging indent in Excel?

[kznmrexcel]

Hi, everyone,
I have a long list of inventory with one column that contains lengthy titles/descriptions. It would be much easier to see the beginning of each line as a hanging indent so that the first word of each of the titles stands out, but I'm not seeing a way to do this. A screenshot as example is shown below. The inventory sheet has 147 line items, so manually indenting each line would be impractical. Is there a way to create a hanging indent that I could apply to all of column C? Please advise.
Thanks in advance,
Karen

 

[Fluff]

Not really, apart from anything else if you change things like the column width, font, font size, the text will re-flow unless you have a linefeed at the end of every line.

 

[Joe4]

I assume that you are talking about column C.

We could add five spaces to the beginning of each entry with some simple VBA like this:

Sub AddIndent()

    Dim cell As Range
  
    Application.ScreenUpdating = False
  
'   Loop through each cell in column C with data
    For Each cell In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
'       Add 5 spaces to beginning of each string
        If Len(cell) > 0 Then
            cell.Value = "     " & cell.Value
        End If
    Next cell
  
    Application.ScreenUpdating = True
  
End Sub

I am not sure if that is the opposite of what you are looking for or not (as we don't see what you want the final output to look like).

But note if you added, say 5 asterisks, i.e. *****, instead of 5 spaces, it certainly would stand out!

 

[kznmrexcel]

Hi, Joe4, thanks for your response. I tried it, but got this error message:

Microsoft Visual Basic
Compile error:
Invalid outside procedure

Any ideas?
TIA,
Kare

 

[Joe4]

Where exactly did you place this VB code?

You want to go into the VB Editor, and from the VB Explorer, right-click on your workbook and select "Insert Module".
Then paste my code there, and then you can run it from View --> Macros --> View Macros.

 

[Peter_SSs]

Another possible option. The result is visually somewhat similar to Joe's but uses a bullet point & doesn't affect the underlying cell value.

Sub BulletPoint()
  Range("C2", Range("C" & Rows.Count).End(xlUp)).NumberFormat = ";;;• General"
End Sub

Here is my sample sheet after applying the code. Note that with C4 selected the text in the formula bar does not include the bullet point.

 

[vcoolio]

Hello Karen,

One of my wife's work colleagues had a similar situation where he had a large amount of text per cell in a column surrounded by many other columns of statistics. He asked if it was possible to have a simple message/dialogue box appear, once a cell was clicked on, with all the cell text visible so that he could simply read it in the message/dialogue box and not the formula bar or so he didn't have to "stretch" the column out to a legible width.

Would something like this work for you?

Cheerio,
vcoolio.

 

[Peter_SSs]

If it is just a visual thing you are after another possibility, though not perfect, might be
- insert two extra columns
- use formulas as described below,
- some careful use of borders,
- turn off gridline display,
- hide the original column.

In my sheet below,
- the text in column C is the same as shown in post #6 above.
- formula in D2 & copied down: =IF(C2="","",LEFT(C2,FIND(" ",C2,3)))
- formula in E2 & copied down: =REPLACE(C2,1,LEN(D2),"")
- column D right aligned
- columns D & E vertically aligned to top of cell

 

[kznmrexcel]

If it is just a visual thing you are after another possibility, though not perfect, might be
- insert two extra columns
- use formulas as described below,
- some careful use of borders,
- turn off gridline display,
- hide the original column.

In my sheet below,
- the text in column C is the same as shown in post #6 above.
- formula in D2 & copied down: =IF(C2="","",LEFT(C2,FIND(" ",C2,3)))
- formula in E2 & copied down: =REPLACE(C2,1,LEN(D2),"")
- column D right aligned
- columns D & E vertically aligned to top of cell

View attachment 39656

Very clever workaround, Peter! Thank you so much. For my purposes, this will work.
Cheers,
Karen

 

[kznmrexcel]

Where exactly did you place this VB code?

You want to go into the VB Editor, and from the VB Explorer, right-click on your workbook and select "Insert Module".
Then paste my code there, and then you can run it from View --> Macros --> View Macros.

Thanks, Joe4! I will play with this as soon as I can to try to get it to work on my end. I appreciate your help with this.
Karen