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 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)
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.
Step 2: Press ENTER. The data in the cell (B4) appear as we thought of.
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
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)
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.
Step 2: Hit ENTER. The text gets in the shape we wanted.
Step 3: Repeat Steps 1 and 2 with individual new_text and you will get a resultant image similar to the image below
Method 3: Using TRIM and REPLACE Function
TRIM function trims all the leading and trailing spaces from a text. Its syntax is
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
The REPLACE function part in the formula works as described in Method 1.
Step 2: Press ENTER. Then we get the outcome that looks similar to the picture below
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
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
Step 2: Press ENTER. Space between Name and ID shows up.
Step 3: Drag the Fill Handle and the rest of the cell gets into the format you want to.
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.