How to Use Code 9 with Excel CHAR Function (2 Easy Examples)

Get FREE Advanced Excel Exercises with Solutions!

You have come to the right place if you are looking for some unique tips to use the Excel CHAR 9 function. There are several ways to use the Excel CHAR 9 function. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purposes. Let’s move on to the article’s main discussion.

What Does Char 9 Mean in Excel?

The CHAR function is a popular Excel function. This function takes the ASCII (American Standard Code for Information Interchange) code as an input. After that, it returns a symbol or character for that ASCII number. English letters, numbers. Symbols, etc., are included in ASCII code.

For example, you can add the ampersand (&) sign by writing the following equation.


CHAR(9) returns a tab between the texts in Excel.

=CHAR (9)

CHAR Code in Excel

Read More: How to Convert Excel ASCII to Char

How to Use Code 9 with Excel CHAR Function: 2 Easy Examples

In this section, I will show you 2 quick examples of how to apply the CHAR 9 function in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft Office 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

I have a dataset with some students’ names, departments, and class ranks that I want to combine into one cell with a tab between each text.

Sample dataset to use CHAR function in Excel

Example 1: Insert Tab Character Using CHAR Function and Code 9

First, I will show you a simple example using the Excel CHAR function. Here you will get a rough idea of how to use this function.

📌 Steps:

  • I have inserted the following formula into cell F5 to count the characters in cell E5.

counting characters using LEN function

  • Now, insert the following formula into cell E5:
  • Thus, you have inserted just a Tab in cell E5. As the tab is just some blank spaces you can’t see any differences in the cell. But you should notice that the value in cell F5 becomes 1 which was 0.

using CHAR 9 function

  • Now, click on the cell E5 and try to select the cell with the mouse cursor and you will see that some blank spaces can be selected which is actually a tab.

visibility of char 9 function

Example 2: Insert Tab Character within String Using CHAR Function

Now, I will show you another example where I will combine some texts from multiple cells with a tab separating them.

📌 Steps:

  • For this, insert the following formula into cell E5.

Insert Tab Character within String Using CHAR Function in Excel

  • Now, drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

dragging fill handle

  • The cells B5, C5, and D5 will be combined, and add a tab between them. But you may notice that the tabs are not visible in the output. So, you may think that the CHAR function is not working but it is actually working. Follow the next steps.

  • Now, create a new column aside from the Formula Output column.
  • Then, copy the cells of the Formula Output column and right-click on cell F5 using Ctrl + C shortcut to open the context menu.
  • Select the Paste Value.

Apply paste value for the selected cells

  • And, you will see a similar output as in the Formula Output column. But, if you click on a cell then you will see there is a tab between the texts. Also, in the formula bar, the tabs between the texts are clearly visible.

Showing tabs inserted with CHAR 9 function of Excel

Read More: Character Codes for CHAR Function in Excel

How to Shorten an Excel Formula Having Multiple CHAR Functions

You may feel weary to insert CHAR(9) repeatedly for multiple tabs if required. I can show you a trick so you can easily call the CHAR function and insert a tab where needed. For this, you just have to insert the tab in a cell using the CHAR function.

📌 Steps:

  • Insert =CHAR(9) in cell G5 so you can use this as a reference to insert the tab anywhere.

Inserting CHAR 9 as formula in an Excel cell

  • Select cell G5 and go to the Name Box of the worksheet.
  • Remove the cell reference number and write tab as the name for the cell.

Renaming an Excel cell

  • Now, you can write only tab instead of writing the whole text  CHAR(9) to insert a tab.
  • You can use the following formula to combine texts from multiple cells with a tab separating them.
  • Inserting formula to combine texts with tab between themAgain use the Paste Value method to visualize the tabs added between the texts.

Character 9 or Tab Space Is Not Visible in Excel?

You may sometimes think that the CHAR 9 is working. Here, I will try to show you how you can recognize whether the function is working or not. Here, in cell E5, the texts of range B5:D5 are combined using the CONCAT function, and in cell F5, the same cell range is combined using CHAR 9 characters between them. But the output is the same for both if you see. To identify the difference, I used the LEN function to calculate the characters of each cell. And, you can see that the count of characters in cell E5 is 20, and for cell F5, it is 22. The extra 2 characters are the 2 tabs inserted between the texts with the formula.

Character 9 Is Not Visible in Excel

Download Practice Workbook

You can download the practice workbook from here:


In this article, you have found how to apply the CHAR 9 function in Excel. I hope you found this article helpful. You can visit our website to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

Osman Goni Ridwan
Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF