How to Align Colon in Excel (4 Easy Methods)

Looking for ways to align colons in Excel? Then, you’ve come to the right place. In fact, this article shows you how to align colon in Excel. Sometimes we need to use the colon inside cells for our convenience. Here, we will take you through four easy and convenient methods on how to align colon in Excel.

How to Align Colon in Excel: 4 Methods

Aligning the colons makes the spreadsheet look better. So, without further delay, let’s jump into the methods of aligning colons in Excel one by one.
Suppose we have an Age List of Students. It includes the Name and Age in Years.

If you look carefully, you can notice that there is a colon between the Name and Age in every cell. But, it’s not aligned with other colons of the remaining lower rows.
Now, we’ll align those colons using various methods.

1. Applying REPT and FIND Functions

Let’s start with applying the REPT and FIND functions. Follow our steps below very carefully to be able to do it.

📌 Steps

First, create a similar new column beside Column B.

Secondly, select cell C5.
Then, write down the formula below.

=REPT(" ",16-FIND(":",B5))&B5

⚙ Formula Breakdown

Here, the FIND function locates the position of the colon ( : ) in the text string in cell B5. Then, it returns the number of the starting position of the colon from the first character of the text string.
Here, we used 16 as the maximum number of characters before the colon plus 1. In our dataset, Emily Pattinson has the highest number of 15 characters before the colon. So we used 16 (15+1).
Then, we used the REPT function. Using this function, we can repeat text a specified number of times. Here, we give a blank space as our Text to repeat. And the Number_times is the output of the FIND function subtracted from 16.
Finally, we concatenated cell B5 at the end of those blank spaces.

Now, press the ENTER key.

After that, apply a monotype font. In this case, we used Courier New.

Next, use the Fill Handle tool and drag it down to get the results in the remaining cells of Column C.

At this moment, select cells in the C5:C14 range.
After that, click on All Borders.

Finally, it looks like the one below.

2. Using Custom Format Cells Option

The most popular and common method to align colons is to use the Custom Format Cells option. Here, we have the Names and Ages in different columns.

Now, we’ll add and align colons at the end of each name. Follow the steps below to do that.

📌 Steps

At first, select cells in the B5:B14 range.
Secondly, go to the Home tab.
Then, click on the Number group.
Now, click on the dialog box launcher arrow at the bottom right corner of the Number.
Or simply, press Ctrl + 1 to open the Custom Format Cells dialog box.