How to Use CHAR(32) Formula in Excel (5 Practical Examples)

The article will show you an application of using 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.


Download Practice Workbook


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.


5 Practical Examples to Apply CHAR(32) Formula in Excel

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.

char 32 excel


1. Putting Space in 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)

char 32 excel method 1

The formula uses LEFT and RIGHT functions to divide the text in 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 B5 has a space in it.

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 Handy Examples)


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:

  • First, make a column to store the transformed IDs and type the following formula in cell D5.

=CONCATENATE(LEFT(B5,12),CHAR(32),RIGHT(B5,12))

char 32 excel method 2

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.

  • Thereafter, use the Fill Handle to AutoFill the lower cells.

char 32 excel method 2

Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.

Read More: How to Convert Excel ASCII to Char (An Easy Way)


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))

char 32 excel method 3

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.

  • Thereafter, 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 Common Uses)


4. Substituting Particular Characters by Space Using CHAR(32)

You will see how to substitute a particular character by 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))

char 32 excel method 4

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.

  • Thereafter, 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: How to Use CHAR(10) Function in Excel (3 Practical Examples)


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)

char 32 excel method 5

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.

  • Thereafter, 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.


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)

char 32 excel

The formula uses LEFT and RIGHT functions to divide the text in B5 in 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.

Finally, you can put a space in all the texts by using the Excel CHAR(32) formula.


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.


Conclusion

Suffice to say, you will learn some basic ideas to use the CHAR(32) formula in Excel after reading this article. If you have any better suggestions or 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 ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo