How Can I Center Text in an Excel Cell?

How Can I Center Text in an Excel Cell?

  • Comments 8
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I center text in an Excel cell?

-- MV

SpacerHey, Scripting Guy! AnswerScript Center

Hey, MV. You know, every now and then we get asked a question and we think to ourselves, “Wow, how many times do you suppose we’ve answered this question?” And, of course, when we go to double-check we find out that we’ve never answered that question; instead, it just seems like we should have answered the question somewhere along the line. We just didn’t remember.

Before you ask, this has nothing to do with the Scripting Guys getting old; in fact, thanks to hard work and clean living none of the Scripting Guys are getting old. And those are not grey hairs; they’ve just been bleached out by the sun.

Note. Sure it’s tough, but if you’re going to make your home in sun-drenched Seattle then having your hair bleached out - so much so that your hair begins to look grey - is just one of those things you have to learn to live with.

At any rate, it turns out that we’ve never told people how to center text in an Excel cell. Well, until now, that is:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1) = "A"
objWorksheet.Cells(1, 2) = "B"
objWorksheet.Cells(1, 3) = "C"

objWorksheet.Cells(1, 2).HorizontalAlignment = -4108

At the risk of spoiling the suspense, we’ll tell you that the only line of code you really need to worry about is the last one; the rest of the script simply creates an Excel spreadsheet and adds some data to it. To do that, the script first creates an instance of the Excel.Application object and then sets the Visible property to True; that gives us a running instance of Excel that’s visible on screen. We use the Add method to add a new workbook to our instance of Excel, then use this line of code to bind to the first worksheet in that workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

Of course, a script that centers text in a cell isn’t all that impressive unless you actually have some text in that cell. Therefore, we use these three lines of code to put the letters A, B, and C into cells A1, B1, and C1, respectively:

objWorksheet.Cells(1, 1) = "A"
objWorksheet.Cells(1, 2) = "B"
objWorksheet.Cells(1, 3) = "C"

In case you’re wondering, that gives us a spreadsheet that looks like this:

Microsoft Excel


Now how do we center the text in one of the cells? Well, this line of code centers the text for cell B1 (that is, cell row 1, column 2):

objWorksheet.Cells(1, 2).HorizontalAlignment = -4108

As you can see, all we have to do is set the value of the HorizontalAlignment property of the cell to -4108; that will center the text in the cell. If we wanted the cell to be right-aligned, we’d set HorizontalValue to -4152; set the value to -4131 to left-align the text.

Run the script, and the finished spreadsheet will look like this, with the text in cell B1 centered:

Microsoft Excel


Another day, another question answered. Speaking of which, how many times have we answered a question about centering text in Excel?

Oh, right: once. The sun appears to be bleaching out our brains as well as our hair ….

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hey,

     How can I center complete row in excel?

  • Try something like this first you center all the cell range you want.

    then you put the value on the left most cell.

    then you look at it  centered. here is the code:

    #

    $Sheet.Range("A1", "B1").MergeCells = $True

    $Sheet.Range("A1", "B1").horizontalalignment = -4108

    $Sheet.Cells.Item(1,1) = "november"

    #

    Let me know if useful.

    Scott

    <A HREF="http://www.techjunkie.tv">Powerhshell code, tips and other tech wisdom</A>

    <A HREF="http://www.scottalvarino.com">Miami I.T. Guru- Remote Support on The Cheap</A>

  • Can I have this again...this time in English?????

  • @Rosemary, this article is written for Network administrators and others who need to be able to use scripting to automate their work with Microsoft Excel. For help on how to use Microsoft Excel, I would suggest the Microsoft Office web site. It has many wonderful tutorials.

    office.microsoft.com/en-us

  • How do I merge and center the title row 1 across columns a-h?

  • In one row the text is justified right. The "center button" does not center in this row whare as it does for all other rows.  

  • how to draw table across cells using vbscripts? Above cmd helped me lot. Thanks for the same. Request to reply me soon.

  • thanks