Is there excel function or simple macro to determine if text is left or right in the cell?

Question

I have a one column with many rows. Each row is containing text. Some text is aligned left, some in middle and some on right.

I would like to have a second column, where each row would receive a value: if the text is aligned on left, then "LEFT", if in middle then "MIDDLE" etc.

Any ideas how to do it?

Answer 1

As far as I know there is no formula for determining cell alignment. You can however create a UDF (User Defined Function) with VBA to achieve this.

For example, if you place the following in a module in VBA...

Function CELL_ALIGNMENT(r As Range) As String

    Select Case r.HorizontalAlignment
        Case -4131
            CELL_ALIGNMENT = "LEFT"
        Case -4108
            CELL_ALIGNMENT = "CENTER"
        Case -4152
            CELL_ALIGNMENT = "RIGHT"
        Case Else
            CELL_ALIGNMENT = "OTHER"
    End Select

End Function

...and then in cell B1 type the following...

=CELL_ALIGNMENT(A1)

...this will tell you the alignment of the text in cell A1.