The article will show you an application of the CHAR function in Excel. Suppose you have a range of IDs in the dataset and you want a space in the middle of them. In that case, it’s convenient to use the CHAR(32) formula in the Excel datasheet. Let’s go through the following sections of the article to see the application of this formula.
What Is CHAR(32) in Excel?
Basically, the CHAR(32) formula returns a space. If the data in your Excel sheet requires spaces between them, putting a space one by one will cost you a huge time. But putting the space in the form of a formula will give you the opportunity to copy the formula all over your dataset and hence it will save you a lot of time.
How to Use CHAR(32) Formula in Excel: 5 Practical Examples
In the dataset, you will see some IDs. They are similar in character length. We will put a space in those IDs using the CHAR(32) formula based on the CHAR function.
1. Putting Space in the Middle of Text Using CHAR(32) with LEFT and RIGHT Functions
In this section, we will put a space in the middle of the IDs. Let’s go through the procedure below for a better understanding.
Steps:
- First, make a column to store the transformed IDs and type the following formula in cell D5.
=LEFT(B5,12) & CHAR(32) & RIGHT(B5,12)
The formula uses LEFT and RIGHT functions to divide the text in cell B5 into two parts. CHAR(32) puts a space between those divided texts.
- After that, press the ENTER button and you will see the ID in cell B5 has a space in it.
- Use the Fill Handle to AutoFill the formula in the lower cells.
Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.
Read More: How to Use CHAR(34) Function in Excel
2. CONCATENATE Function with CHAR(32) to Insert Space in Middle of Text
We will be doing the same operation here as we did in Method 1 but using a different function. Please see the description below.
Steps:
- Create a column to store the transformed IDs and type the following formula in cell D5.
=CONCATENATE(LEFT(B5,12),CHAR(32),RIGHT(B5,12))
The formula uses LEFT and RIGHT functions to divide the text in B5 into two parts. CHAR(32) returns a space and the CONCATENATE function joins them all.
- After that, press the ENTER button and you will see the ID in B5 has a space in it.
- Apply the Fill Handle to AutoFill the rest of the cells.
Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.
Read More: How to Use Code 9 with Excel CHAR Function
3. Replacing One or Several Characters by Space Using Excel CHAR(32) Formula
In this section, you will see how to use the CHAR(32) formula to replace characters from a certain position. Let’s go through the discussion below.
Steps:
- First, make a column to store the transformed IDs and type the following formula in cell D5.
=REPLACE(B5,FIND("a",B5),2, CHAR(32))
The formula uses the FIND function to find out the position of the first a in B5 and replace it with a space using the REPLACE function and CHAR(32) formula.
- After that, press the ENTER button and you will see the text ae in the ID of B5 replaced by a space.
- Drag the Fill Handle to AutoFill the lower cells.
Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.
Read More: How to Use CHAR(10) Function in Excel
4. Substituting Particular Characters by Space Using CHAR(32)
You will see how to substitute a particular character with the CHAR(32) formula. Please follow the steps below.
Steps:
- First, make a column to store the transformed IDs and type the following formula in cell D5.
=SUBSTITUTE(B5,"3",CHAR(32))
The formula uses the SUBSTITUTE function to substitute 3 by a space with the help of the CHAR(32) formula.
- After that, press the ENTER button and you will see the formula replaces the number 3 in the ID of B5 by a space.
- Use the Fill Handle to AutoFill the lower cells.
Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.
Read More: Character Codes for CHAR Function in Excel
5. Joining Two Texts by Space with Excel CHAR(32) Formula
We can also use the CHAR(32) formula to join two or more texts by a space. Please follow the procedure below.
Steps:
- First, make a column to store the transformed IDs and type the following formula in cell D5.
=CONCAT(B5, CHAR(32),C5)
The formula uses the CONCAT function to join the texts in B5, Space (CHAR(32)), and texts in C5.
- After that, press the ENTER button and you will see the ID and the Names join together by a space.
- Apply the Fill Handle to AutoFill the lower cells.
Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.
Using Space as String: An Alternative to Excel CHAR(32) Formula
If you don’t want to use the CHAR(32) formula, you can simply use the space as a text. Please go through the description below.
Steps:
- First, make a column to store the transformed IDs and type the following formula in cell D5.
=LEFT(B5,12) & " " & RIGHT(B5,12)
The formula uses LEFT and RIGHT functions to divide the text in B5 into two parts. " " is used instead of the CHAR(32) formula which puts a space between those divided texts. The formula will do the job of the formula we used in Method 1.
- After that, press the ENTER button and you will see the ID in B5 has a space in it.
- Thereafter, use the Fill Handle to AutoFill the lower cells.
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
You will learn some basic ideas to use the CHAR(32) formula in Excel after reading this article. If you have any better suggestions, questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website.
