Maruf Islam
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 an Excel cell. Basically, 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. So, we want to make a proper structure of Name and ID.
Download Practice Workbook
You can download the practice workbook from here:
4 Easy Ways to Add Space Between Text in a Cell in Excel
Here, I will demonstrate 4 suitable methods with detailed steps on how to add space between text in an Excel cell. Furthermore, for this session, I’m going to use Microsoft 365 version.
Method 1: Using REPLACE Function to Introduce Space Between Text
The REPLACE function replaces specified parts of the text string with a newly 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.
Now, follow the steps given below.
Steps:
- Firstly, type the formula in any blank cell (C5).
=REPLACE(B5,5,0," ")
Formula Breakdown
- Here, B5 is the old_text reference. Actually, we have the text “JaneDoe123” in cell B5. Eventually, we want the text as “Jane Doe123”.
- So, we want a space starting character start_num “5” (i.e after Jane).
- However, we do not want any character to replace, so num_chars are “0”.
- Also, the new_text will be the same.
- Subsequently, press ENTER. As a result, the data in the cell (B5) appear as we thought.
- After that, for the C6 cell use the following formula (“Kit” has “3” letters, including the space, it will be “4”).
=REPLACE(B6,4,0," ")- Then, press ENTER.
- In the same way, repeat the above steps with individual start_num and num_chars. Then, we will get a picture similar to the picture below.
Read More: How to Add Blank Space Using Excel Formula (6 Methods)
Method 2: Applying SUBSTITUTE Function to Add Space Between Text
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 the 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.
So, let’s see the steps.
Steps:
- First, insert the formula in any blank cell (C5).
=SUBSTITUTE(B5,"JaneDoe123","Jane Doe 123",1)- Then, hit ENTER. As a result, the text gets in the shape we wanted.
Formula Breakdown
- In the formula, B5 is the old_text reference.
- Here, we have the text “JaneDoe123” in cell B5. But we want the text as “Jane Doe 123”.
- Furthermore, the [instance_num] is “1”, as we have only one instance in the reference cell B5.
- Similarly, write the certain formula for the B6 cell in the C6 cell. As another example, we have attached the corresponding formula below.
=SUBSTITUTE(B6,"KitHarington124","Kit Harington 124",1)- Subsequently, press ENTER.
- Lastly, repeat the above steps with individual new_text and you will get a resultant image similar to the image below.
Read More: How to Add Space between Rows in Excel
Similar Readings
Method 3: Merging TRIM and REPLACE Functions in Excel
TRIM function trims all the leading and trailing spaces from a text. Actually, its syntax is:
TRIM (text)Eventually, we have to add spaces, do not trim them. Therefore, to solve this, we combine TRIM and REPLACE functions. Basically, the 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.
Steps:
- Now, click on any blank cell (C5) and paste the formula.
=TRIM(REPLACE(B5,5,0," "))Here, the REPLACE function part in the formula works as described in Method-1.
- Then, press ENTER. So, we get an outcome that looks similar to the picture below.
- In the same way, write a certain formula for the B6 cell. As another example, we have attached the corresponding formula of the C6 cell below.
=TRIM(REPLACE(B6,4,0," "))- Then, press ENTER.
- At this time, repeat the above steps following Method-1 instructions for REPLACE function. After that, you will get an organized dataset like the one below.
Read More: How to Add Space between Numbers in Excel (3 Ways)
Method 4: Combining TRIM, REPLACE, MIN and FIND Functions
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”. Now, to achieve the purpose, we can use a combination of TRIM, REPLACE, MIN, and FIND functions. So, let’s follow the steps below.
Steps:
- Firstly, select any blank cell (C5) and enter the formula.
=TRIM(REPLACE(B5,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B5&"1234567890")),0," "))- Secondly, press ENTER. As a result, a space between Name and ID shows up.
Formula Breakdown
- Firstly, B5&”1234567890″—> here the Ampersand operator (&) will join them.
- Output: “JaneDoe1231234567890”.
- Secondly, FIND({1,2,3,4,5,6,7,8,9,0},”JaneDoe1231234567890″)—> the FIND function will find the position (counting from “J”) of given numbers within the certain text.
- Output: {8,9,10,14,15,16,17,18,19,20}.
- Thirdly, MIN({8,9,10,14,15,16,17,18,19,20})—> the MIN function will give the lowest number from the given array.
- Output: 8.
- Fourthly, REPLACE(B5,8,0,” “)—> here the REPLACE function will introduce a space after the 7th number character of the B5 cell.
- Output: “JaneDoe 123”.
- Lastly, the TRIM function will remove all the extra spaces except a single space from the above text.
- After that, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
Finally, the rest of the cells get into the format you want.
Read More: How to Find and Replace Space in Excel (5 Methods)
How to Include Space Between Numbers and Text in Excel
Now, suppose you have the ID first and then the Name. And you want a space between the ID and Name in your dataset. For example, you want the text “123JaneDoe” to display as “123 JaneDoe”.
This is a little bit difficult situation. So, to achieve the purpose, you might use a combination of TRIM, REPLACE, MAX, IFERROR, ROW, INDIRECT, LEN, and FIND functions. So, you can guess that this is going to be a complex formula. Now, let’s see the steps below.
Steps:
- Firstly, select any blank cell (C5) and enter the formula.
=TRIM(REPLACE(B5,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B5,ROW(INDIRECT("1:"&LEN(B5)))),0))+1,0," "))- Secondly, press ENTER. As a result, the space between ID and Name shows up.
Formula Breakdown
- First, LEN(B5)—> gives 10.
- Then, INDIRECT(“1:”&10)—> returns the array of 1:10.
- After that, the ROW function will give the row numbers of the above array.
- Output: {1;2;3;4;5;6;7;8;9;10}.
- Subsequently, the FIND function will find the position of given numbers within the B5 cell.
- Eventually, the IFERROR function will convert all the error values into 0.
- Then, the MAX function will give the maximum number from the given array.
- Output: 4.
- Consequently, REPLACE(B5,4,0,” “)—> here the REPLACE function will introduce a space after the 3rd number character of the B5 cell.
- Output: “123 JaneDoe”.
- Lastly, the TRIM function will remove all the extra spaces except a single space from the above text.
- Then, drag the Fill Handle icon and the rest of the cell gets into the format you want.
Practice Section
Now, you can practice the explained methods by yourself.
Conclusion
In the article, we describe the usage of functions to add space between text in an Excel cell. 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. You can visit our website Exceldemy to learn more Excel-related content.
I used Method 3 and it worked great, however, how do I get rid of the column that is incorrect (without the edits/added space). Since they are dependent on each other, an error pops up when trying to remove the column that needed editing in the first place.
Hello Emily,
It is very motivating for us when someone benefits from using our method. Now getting back to your query. You can use a simple copy-paste feature to overcome this issue. Let’s see the process below for a better understanding.
• Firstly, select cells in the C5:C13 range.
• Then, press the CTRL key followed by the C key on the keyboard.
This command copies the whole range.
• After that, right-click on cell E5.
• In the context menu, select Values (V) in the Paste Options.
You can see the data pasted in the new place.
• Additionally, select cells in the B5:B13 range and C4:C13 range also.
• Therefore, delete them using the Delete button.
• At this time, press CTRL+C to copy the cells in the D5:D13 range.
• Henceforth, go to cell B5 and paste them by pressing CTRL+V.
Finally, you get the desired result.