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.
Download Practice Workbook
You can download the practice workbook from here:
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.
How to Apply CHAR Function with Code 9 to Generate Tab Character in Excel
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 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.
Example 1: Insert Tab Character Using CHAR Function and Code 9
First, I will show you a simple example using the Excel CHAR function, so, you will get a rough idea of how to use this function.
- Here, I have inserted the following formula into cell F5 to count the characters in cell E5.
- First, there was no input in cell E5, so the LEN function gave 0 as output.
- 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 so you can’t see any differences in the cell. But you should notice that the value in cell F5 becomes 1 which was 0
- 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
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.
- For this, insert the following formula into cell E5.
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- So, 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.
- Then, select the Paste Value
- And, you will see a similar output as in the Formula Output But, if you click on a cell then you will see there is a tab between the texts. Also, in the formula bar, the tab between the texts are clearly visible.
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. Follow the steps below:
- First, insert =CHAR(9) in cell G5 so you can use this as a reference to insert the tab anywhere.
- Then, select cell G5 and go to the Name Box of the worksheet.
- Then, remove the cell reference number and write “tab” as the name for the cell.
- Now, you can write only “tab” instead of writing the whole text “CHAR(9)” to insert a tab.
- So, you can use the following formula to combine texts from multiple cells with a tab separating them.
- Now, again 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 cell 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.
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 ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.