Uses of CHAR Function & CHAR(10) NOT Working - Solved

In my last article, I have discussed about the CHAR function. This function returns the character from the ASCII code.

We learned about how to use CHAR Function ?

Now the second question is

Where to use CHAR function ?

Today I will give you few examples where to use CHAR function in Excel.

Case 1 :

Suppose you need to print the alphabet ‘A’ to ‘Z’ in column. The simple technique is you select a cell and type ‘A’, press enter type ‘B’ and so on. Like numbers you can not drag the fill handler and get the alphabets. Here you can implement CHAR function.

How to get alphabets by dragging fill handler ?

There are various methods to it but we will use CHAR function. So lets do it step by step

1.       You need to know the ASCII code of ‘A’

2.       The ASCII code for capital ‘A’ is 65 and small ‘a’ is 97. How do I know ASCII code of ‘A’ is 65 ? In my next article I will show you how you can get the ASCII value of any character. For this purpose ASCII code of  capital ‘A’ is 65.

3.       Type 65 in any cell (suppose the cell is A1) in Excel then drag it by fill handler till 90. (Because there are 26 alphabets available.)

4.       Now, type =char(A1) and press enter.

5.       You will see A is printed here.

6.       Now you can drag the cell by fill handler, it will show you the alphabets.

Case 2 :

You may be noticed that using double quotes sometimes are very confusing. For example I want to concatenate a string “The movie “ and the movie name “XXX” and the last string “was good”.

Now you can do this by typing the formula

="The movie """&A2&""" was good"

But you notice the double quotes are very confusing.

Therefore, we will use CHAR function to get an easily understandable formula like

="the movie "&CHAR(34)&A2&CHAR(34)&" was good"

Please compare these two formulas, you will easily find out which one is easy to use in Excel.

How to use CHAR Function in Excel

Case 3 :

Let suppose you got Address field like Address 1, Address 2 & Address 3. Now you need to concatenate it to print addresses in one cell.

Normally if you use CONCATENATE or CONCAT function you will get combined address but you have to insert spaces in between the address and then you need to COPY it and PASTE SPECIAL it. 

After that you should manually enter a line break to get the desired output.

Using CHAR function, you can do it very easily.

As shown in the below picture I have Address 1, Address 2 & Address 3, I have used CONCAT function to add this strings and used CHAR function with ASCII value 10 which gives us a ‘Line Break’. (For MAC use ASCI Code 13).

Formula : =CONCAT(A3,CHAR(10),B3,CHAR(10),C3)

CHAR(10) not working !!!

Kindly Note : Don’t get anxious if CHAR(10) don’t put any line break. The above formula works fine. You need to Click on Wrap Text button to get the result.

How to use CHAR Function in Excel

Like the above examples there are many formulae where you can use CHAR function. If any questions you are having in your mind, please feel free to contact me through Contact Us form.

Hope you have enjoyed this article.

Thank you for reading….