How to Add Space Between Text in Excel Cell (4 Easy Ways)

It is a common phenomenon in Excel that we import data from external sources. By doing so, we encounter entries void of spaces. In this article, we discuss methods to add space between text in Excel cell. We can add various sorts of spacing formats using functions like TRIM, REPLACE, FIND, MIN, and SUBSTITUTE.

Suppose, we import Name and ID data in Excel that looks like the image below

dataset

Dataset for Download

4 Easy Ways to Add Space Between Text in Excel Cell

Method 1: Using REPLACE Function

The REPLACE function replaces specified parts of the text string with a new assigned text string. Its syntax is

REPLACE (old_text, start_num, num_chars, new_text)

old_text; refers to any reference cell you want the text to be replaced.

start_num; declares from which number of character replacing will happen.

num_chars; defines how many characters will get replaced.

new_text; is the text that ultimately will be in the place of replaced characters.

Step 1: Type the formula in any blank cell (C4)

=REPLACE(B4,5,0,” “)

Here, B4 is the old_text reference. We have text “JaneDoe123” in cell B4. We want the text as “Jane Doe123”. So, we want a space starting character start_num “5” (i.e after Jane). We do not want any character to replace, so num_chars are “0”. And the new_text will be the same.

replace function

Step 2: Press ENTER. The data in the cell (B4) appear as we thought of. 

replace function result

Step 3: Repeat Steps 1 and 2 with individual start_num and num_chars. Then we will get a picture similar to the picture below

final replace function result

Method 2: Using SUBSTITUTE Function

For replacing text in a specific location, we use the REPLACE function whereas we use the SUBSTITUTE function to substitute any specific text.

The syntax of SUBSTITUTE  function is

SUBSTITUTE(text, old_text, new_text, [instance_num])

text; directs to any reference cell you want the text to substitute.

old_text; defines the text in the reference cell you want to substitute.

new_text; declares the text you the old_text to substitute with.

[instance_num]; defines the number of occurrences in old_text you want to substitute.

Step 1: Insert the formula in any blank cell (C4)

=SUBSTITUTE(B4,”JaneDoe123″,”Jane Doe 123″,1)

In the formula, B4 is the old_text reference. We have text “JaneDoe123” in cell B4. We want the text as “Jane Doe 123”. And the [instance_num] is “1”, as we have only one instance in the reference cell B4.

substitute function

Step 2: Hit ENTER. The text gets in the shape we wanted.

substitute function result

Step 3: Repeat Steps 1 and 2 with individual new_text and you will get a resultant image similar to the image below

substitute function final result

Method 3: Using TRIM and REPLACE Function

TRIM function trims all the leading and trailing spaces from a text. Its syntax is

TRIM (text)

But we have to add spaces not to trim them. To solve this, we combine TRIM and REPLACE functions to do so. REPLACE function treats the text as it does in Method 1. And the TRIM function only removes the spaces placed in leading or trailing (in case the data have spaces) and returns with a single space.

Step 1: Click on any blank cell (C4) and paste the formula

=TRIM(REPLACE(B4,5,0,”  “))

The REPLACE function part in the formula works as described in Method 1.

trim and replace function

Step 2: Press ENTER. Then we get the outcome that looks similar to the picture below

trim and replace function result

Step 3: Repeat Steps 1 and 2 following Method 1 instructions for REPLACE function. After that, you will get an organized dataset like the one below

trim and replace function final result

Method 4: Using TRIM REPLACE MIN and FIND Function

What if we want a space between the Name and ID in our dataset. For example, we want the text “JaneDoe123” to display as “JaneDoe 123”. To achieve the purpose, we can use a combination of TRIM, REPLACE, MIN, and FIND functions.

Step 1: Select any blank cell (C4) and enter the formula

=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&”1234567890″)),0,” “))

trim replace min and find function

Step 2: Press ENTER. Space between Name and ID shows up.

trim replace min find function result

Step 3: Drag the Fill Handle and the rest of the cell gets into the format you want to.

trim replace min find function final result

Conclusion

In the article, we describe the usage of functions to add space between text. The REPLACE function adds space to a specific location defining characters whereas the SUBSTITUTE function substitutes any text with a given text. Other combinations of functions work depending on a specific condition. Hope you find the above-explained methods worthy of your quest. Comment, if you need further clarifications and have something to add.


Further Readings:

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo