Osman Goni Ridwan
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(38)CHAR(9) returns a tab between the texts in Excel.
=CHAR (9)
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.
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.
=LEN(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:
=CHAR(9)- 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.
- 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.
📌 Steps:
- For this, insert the following formula into cell E5.
=B5&CHAR(9)&C5&CHAR(9)&D5
- 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.
- 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.
- 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.
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.
- 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.
- 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.
=B5&tab&C5&tab&D5
Again use the Paste Value method to visualize the tabs added between the texts.
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 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.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
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.
