Count spaces at start of cell in excel

Question

I have lots of cells each with different levels of indentation on them. I wish to count the number of spaces before each word and then based on the number of spaces the text inside will be formatted (eg two spaces at start is bold and size 12). The spaces must be kept and not deleted. The amount of spaces varies on each row and the number of rows can also vary. The cells may contain more than one word but it is only the initial spaces that I am interested in.

could anybody help with any suggestions as to how to achieve this.

thanks

Answer 1

Hi, the below checks cells in column A from row two (A2) to the last row used in the active sheet. The case statement is where you put you format code depending on the number of spaces found. You will need to add case statements for each number of spaces you want to format for. Hope this gets you started.

Public Sub Indent()
Dim x, y, lngSpaceCount As Long
Dim rng As Range
Dim c
Dim blnCharFound As Boolean

With ActiveSheet
    .Range("A2:A2").Activate
    x = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Set rng = .Range("A2:A" & Trim$(Str$(x)))
    For Each c In rng
        If Len(c.Value) > 0 Then
            blnCharFound = False
            lngSpaceCount = 0
            For y = 1 To Len(c.Value)
                If (Mid$(c.Value, y, 1) = " ") And Not blnCharFound Then
                    lngSpaceCount = lngSpaceCount + 1
                Else
                    blnCharFound = True
                End If
            Next
            Select Case lngSpaceCount
            Case 2
                c.Font.Name = "Arial"
                c.Font.Size = 12
                c.Font.Bold = True
            Case 3
                c.Font.Name = "Arial"
                c.Font.Size = 14
                c.Font.Bold = True

            End Select
        End If
    Next
End With
End Sub

Answer 2

This will tell you using an equation
=Len(A1)-Len(Trim(A1))