How to verify cell alignment left, right, center or other?

[Fabian Chaverri]

I need to find a way to identify if a cell was aligned to the right, left or center. I was able to validate if the cell contained either a values or text (istext, isnumber) or if it contained certain types of format (cell("format",_) or cell("type",_)). Does anybody know a way? Thanks!

 

[SirJB7]

Hi, Fabian Chaverri!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


I'm afraid that Excel don't exposes cell or range alignment properties to be used by formulas, so you'll need a UDF (user defined function) that does the job. Here's the code:


-----

[pre]

Option Explicit

Function sAlignment(pRng As Range) As String
' constants
Const ksAlign = "X, No alignment, Left aligned, Centered, Right aligned"
' declarations
Dim sAlign() As String, I As Integer
' start
sAlign = Split(ksAlign, ",")
' process
Select Case pRng.Cells(1, 1).HorizontalAlignment
Case xlGeneral
I = 1
Case xlLeft
I = 2
Case xlCenter
I = 3
Case xlRight
I = 4
End Select
' end
sAlignment = sAlign(I)
End Function

[/pre]
-----


Use it as:

=sAlignmentType(A1)


Just advise if any issue.


Regards!

 

[NARAYANK991]

Hi Fabian ,


To add to whatever has been posted , the CELL function with "prefix" for the first parameter , returns information about the cell alignment , as in :


=CELL("prefix",A1)


However , there are two problems with this function :


1. This works only for cells which contain text ; it does not work with cells which have numbers in them


2. Any time you change the alignment , you need to either recalculate the cell which has the CELL function in it , by pressing F2 and ENTER , or recalculate the entire worksheet by pressing F9.


Narayan

 

[SirJB7]

@NARANYANK991

Hi!

Despite of that (i.e., it works only for cell containing text) it retrieves the same character (apostrophe ') for both no aligned and left aligned cells.

Regards!

 

[NARAYANK991]

Hi Pablo ,


You are right , but have you considered that for TEXT , the default is left-aligned ?


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Yes, I did, but if the attribute of the HorizontalAlignment property for that cell is different (0 for non aligned, -4131 or &HEFDD& for left, -4108 or &HEFF4& for centered and -4152 or &HEFC8& for right), it should retrieve a different result, a different character in this case as it doesn't retrieve the actual value. Well, it's a "prefix" not an "alignment" parameter after all.

Otherwise it's so badly implemented that it shouldn't be used at all, IMHO.

Regards!

 

[NARAYANK991]

Hi Pablo ,


You will have to take Microsoft to task for that.


If you re-read the first sentence by OP :


I need to find a way to identify if a cell was aligned to the right, left or center.


I did not find a no-aligned requirement.


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Literally read and taken, you're right, Fabian Chaverri asked what you wrote, so he'd safely use the CELL function with the "prefix" parameter. Of course assuming the only text constraint.

In any case he has the choice, as OP always have. My advice is clear.

Regards!