How to Align Colon in Excel (4 Easy Methods)

Looking for ways how to align colon 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.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


4 Methods to Align Colon in Excel

Aligning the colons makes the spreadsheet looks 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.

how to align colon in excel

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

How to Align Colon in Excel

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

📌 Steps

  • At first, create a similar new column beside Column B.

How to Align Colon in Excel Applying REPT and FIND Functions

  • 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.

How to Align Colon in Excel Applying REPT and FIND Functions

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

How to Align Colon in Excel Applying REPT and FIND Functions

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

How to Align Colon in Excel Applying REPT and FIND Functions

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

How to Align Colon in Excel Applying REPT and FIND Functions

  • Finally, it looks like the one below.

How to Align Colon in Excel Applying REPT and FIND Functions

Read More: How to Apply Center Horizontal Alignment in Excel (3 Quick Tricks)


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.

How to Align Colon in Excel Applying Custom Format Cells Option

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.

How to Align Colon in Excel Applying Custom Format Cells Option

  • At this point, the Format Cells dialog box opens.
  • Now, select Custom from the menu.
  • Then, type @ * \: in the box.
  • Finally, click OK.

How to Align Colon in Excel Applying Custom Format Cells Option

  • Instantly, the data table looks like below.
  • Here, a colon is added automatically at the end of each name and is right aligned.

How to Align Colon in Excel Applying Custom Format Cells Option

Read More: How to Change Alignment to the Right in Excel (5 Quick Methods)


Similar Readings


3. Inserting a New Column to Align Colon in Excel

How to Align Colon in Excel Inserting a New Column

We can align colons by inserting a new column into our dataset. Follow the steps below to do that.

📌 Steps

  • First, right-click on Column C.
  • Secondly, select Insert from the options.

How to Align Colon in Excel Inserting a New Column

  • Our previous action inserts a new column between our old two columns.

How to Align Colon in Excel Inserting a New Column

  • At this point, give a header to the newly created column. We did it as Colon.
  • After that, input a colon in each cell in the C5:C14 range.

How to Align Colon in Excel Inserting a New Column

  • Now, reduce the Column Width of Column C.
  • Then, select cells in the B5:B14 range and select Align Right.
  • Similarly, select cells in the D5:D14 range and select Align Left.

How to Align Colon in Excel Inserting a New Column

  • To make it more visually appealing, give just Outside Borders to the table.

Inserting a New Column

Read More: How to Left Align in Excel (3 Handy Ways)


4. Employing Align Right Option to Align Colon in Excel

Employing Align Right Option

Another alternative to solve the problem is to employ the Align Right option. Follow the steps below.

📌 Steps

  • Firstly, add a colon at the end of the Name in each cell in our previous dataset.

Employing Align Right Option

  • Secondly, select cells in the B5:B14 range.
  • Then, select Align Right from the Alignment group.

Employing Align Right Option

  • At this moment, our dataset looks like the one below.
  • Here, the colons get aligned at the right side of the cells.

Employing Align Right Option

  • Finally, remove All Borders and apply just Outside Borders to the data table.
  • In this case, it makes the sheet more presentable.

Employing Align Right Option


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo