How to Add Space between Numbers in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with numbers in Excel, you may need to insert space(s) between numbers to have a clear look. Sometimes, the numbers of a cell are too long. For that, it becomes difficult to read. Spaces between numbers give us a clear look at that cell or range of cells. In this tutorial, we are going to show you how to add space between numbers in Excel with suitable examples and proper illustrations. In the picture below, an image has been added to give an idea of how easy it becomes to read numbers if we add proper spacing.

Overview image of how to add space between numbers in excel


How to Add Space between Numbers in Excel: 3 Effective Approaches

To add space between numbers, we are going to demonstrate three effective methods that you can implement into your dataset. We recommend you learn and apply all these methods to your dataset. I hope it will solve your problem. To demonstrate this tutorial, we are using the following dataset that contains population data of 6 random cities in the United States.

Dataset for adding space between numbers in Excel

Now, our goal is to add space between the digits or a couple of digits of those numbers. Let’s explore the methods one by one for adding spaces between those numbers.


1. Use of Formulas to Add Space Between Numbers

Now, we will show four Excel formulas to insert space between numbers. All these formulas are composed of some Excel functions. Now, if you don’t have many ideas about these functions, click the links to those functions. Surely, it will come in handy in the future.


1.1 Use of REPLACE Function to Add Space Between Numbers

In Microsoft Excel, we use the REPLACE function to replace a specific portion of a text. The replacement performs based on the starting number, the number of characters, and the text you want to add. We are going to use this function to denote the position of that space and insert that.

The Generic Formula:

=REPLACE(old_text,start_num,num_chars,” β€œ)

Now, from the dataset, we are going to add space in the middle of the numbers. To do that, follow the steps below.

πŸ“Œ Steps

  • First, type the following formula in Cell D5:
=REPLACE(D5,4,0," ")

Inserting Formula containing REPLACE function in Cell D5

  • Then, press Enter. As a result, you will see that a space has been added between the 3rd and 4th digits of the number in cell C5.

Results of using Replace function to add space between numbers

3. After that, drag the Fill Handle icon over the range of cells D6:D10.

Using Fill Handle to autofill rest of the cell and add space between numbers

As you can see, we have successfully added space between those numbers.


1.2 Use of CONCATENATE, LEFT, and RIGHT Functions for Adding Space Between Numbers

Now, this formula is a combination of three Excel functions. If we want to add any space at a particular place, we need to calculate the position from both the right and left sides. The LEFT function will count the position from the left and extract numbers. The RIGHT function will count from the right and will extract it. In the middle of those numbers, we will add a space. And finally, the CONCATENATE function will merge them all.

The Generic Formula:

=CONCATENATE(LEFT(text,[num_chars])&” β€œ&RIGHT(text,[num_chars]))

Now, from the dataset, we are going to add space in the middle of the numbers.

πŸ“Œ Steps

  • First, type the following formula in Cell D5:
=CONCATENATE(LEFT(C5,3)&" "&RIGHT(C5,3))

Inserting CONCATENATE, LEFT, and RIGHT Functions in cell D5 for Adding Space Between Numbers

  • Then, press Enter. As a result, the number in cell C5 will have a space between the 3rd and 4th digits.

Results after applying the CONCATENATE, LEFT & RIGHT Function

  • After that, drag the Fill Handle icon over the range of cells D6:D10.

Using Fill Handle to autofill formula in order to add space between numbers

In the end, you can see there are spaces in the middle of these numbers.

πŸ”Ž How Does the Formula Work?

We are doing this breakdown for Cell C5

➀ LEFT(C5,3)

This function returns: 133

➀ RIGHT(C5,3)

This function returns: 546

➀ CONCATENATE(LEFT(C5,3)&” β€œ&RIGHT(C5,3))

Finally, the CONCATENATE function will return: 133 546

Read More: How to Count Space Before Text in Excel


1.3 Use of MID Function along with CONCATENATE, LEFT, and RIGHT Functions to Add Space Between Numbers

If you want to separate the numbers into three parts or more, you can add the MID function in the previous formula. How to do that? Follow the steps below!

πŸ“Œ Steps

  • First, type the following formula in Cell D5:
=CONCATENATE(LEFT(C5,2)&" "&MID(C5,3,2)&" "&RIGHT(C5,2))
  • Then, press Enter. Consequently, you will get the following result.

Inserting CONCATENATE, MID, LEFT & RIGHT Function to get space between numbers

  • After that, drag the Fill Handle icon over the range of cells D6:D10.

Using Fill Handle to autofill formula in order to add space between numbers

πŸ”Ž How Does the Formula Work?

We are doing this breakdown for Cell C5

➀ LEFT(C5,2)

This function returns : 13

➀ MID(C5,3,2)

This function returns :Β 35

➀ RIGHT(C5,2)

This function returns : 46

➀ CONCATENATE(LEFT(C5,2)&" "&MID(C5,3,2)&" "&RIGHT(C5,2))

Finally, the CONCATENATE function will return: 13 35 46


1.4 Use of TEXT Function for Addition of Space Between Numbers

Now, we are using the TEXT function to change the format. You can add spaces between numbers. It will change the general format of that number.

We have two ways of doing that. The first one is the format β€œ## ## ##” and the other one is β€œ00 00 00β€œ. You can choose any of that and implement this to your dataset. It will return a similar result.

The Generic Formula:

=TEXT(value,”## ## ##”)

Or,

=TEXT(value,”00 00 00β€³)

We are going to separate the numbers into three parts using spaces.

πŸ“Œ Steps

  • First, type the following formula in Cell D5:
=TEXT(C5,"## ## ##")

Or,

=TEXT(C5,"00 00 00")

Inserting TEXT function in cell D5

Note: As the numbers in our dataset have 6 digits each, and we want to insert a space after every 2 digits, we have used β€œ## ## ##” or, β€œ00 00 00”. The format will depend on the number of digits, space positions, etc.

  • Then, press Enter.

Results after using Text Function in cell D5

  • After that, drag the Fill Handle icon over the range of cells D6:D10.

Using Fill Handle to autofill formula in order to add space between numbers

As you can see, we have inserted spaces and separated the numbers into three parts.

Read More: How to Add Space Between Text in Excel Cell


1.5 Use of TEXT, LEN, and REPT Functions to Add Between Numbers

Here, this formula is a combination of three functions. Earlier, we discussed the TEXT function. The LEN function gives us the length of any cell values. And the REPT function is generally used to repeat a text or a character a defined number of times in a cell.

The Generic Formula:

=TEXT(value,REPT(β€œ0 β€œ,LEN(text)))

We are going to add spaces after every single number using this formula.

πŸ“Œ Steps

1. First, type the following formula in Cell C5:

=TEXT(C5,REPT("0 ",LEN(C5)))

Inserting TEXT, LEN, and REPT Functions to Add Between Numbers

2. Then, press Enter.

Results after inserting TEXT, LEN, and REPT Functions

3. After that, drag the Fill Handle icon over the range of cells D6:D10.

Using Fill Handle to autofill formula in order to add space between numbers

As you can see, we have added spaces after every single number.

πŸ”Ž How Does the Formula Work?

We are doing this breakdown for Cell B5

➀ LEN(B5)

This function returns: 6

➀ REPT(β€œ0 β€œ,LEN(B5))

This function returns : 0 0 0 0 0 0Β 

➀ TEXT(B5,REPT(β€œ0 β€œ,LEN(B5)))

Finally, our number will be: 1 3 3 5 4 6Β 


Similar Readings


2. Using Number Formatting to Add Space Between Numbers

Now, this method is pretty simple to use. This method works like the TEXT function we discussed earlier. We will give our numbers a format. We can use spaces here between numbers.

πŸ“Œ Steps

  • First, select the range of cells C5:C10.

Selecting cells with numbers

  • Then, go to Home Tab. From there, you can find a dropdown menu. Now, click on that.
  • Then, select More Number Formats.

Opening Format Menu and clicking More Number Formats

  • Then, click on Custom.

Clicking on Custom format Category

  • Now, in the Type box, type 00 00 00. You can also see the sample above.

Typing the number format with space.

  • Finally, click on OK.

Custom formatted Numbers with space

Here, you can see spaces between numbers. They are divided into three parts.


3. Using VBA Codes to Append Space Between Numbers in Excel

Now, if you know about Excel’s VBA codes, this method will surely work for you. We are providing you with two VBA codes.


3.1 Adding Space after Each Digit

This method will add a space after each digit as we did with formulas. To know more, follow the steps below.

πŸ“Œ Steps

  • First, press ALT+F11 on your keyboard to open the VBA editor.
  • Then, click on Insert >> Module.

Inserting a new module on vba editor

  • Then, type the following code:
Sub InsertSpace()
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("C5", Range("C" & Rows.Count).End(xlUp))
r = Trim(Replace(StrConv(r, vbUnicode), Chr(0), " "))
Next r
Columns("C").AutoFit
Application.ScreenUpdating = True
End Sub
  • Now, select the range of cells C5:C10.

Selecting cells C5:C10

  • Then, press ALT+F8. It will open the Macro dialog box.

Opening macro dialog box

  • After that, click on Run.

Clicking on Run to add space

  • As a result, you will see added spaces after each digit.

Results after running VBA code


3.2. Adding Space According to User’s Choice

Now, this formula is much more useful and effective. You can add space in any position according to your choice. In the user-defined function, all you have to do is choose the cell and the position of the space.

πŸ“Œ Steps

  • First, press ALT+F11 on your keyboard to open the VBA editor.
  • After that, click on Insert > Module.
  • Then, type the following code:
Function Space(cell As Range,position As Integer)
Space=Left(cell.Value,position)& " "&Right(cell.Value,Len(cell.Value)-position)
End Function
  • Here, we have essentially created a custom function namedΒ SpaceΒ which takes 2 arguments. The 1st argument will be the cell on which the function will work. The 2nd argument is the number of digits after which space will be added.
  • Next, save the module by clicking CTRL+S.
  • Then, come back to the main worksheet.
  • Now, type the following formula in Cell D5:

=Space(C5,2)

Using Custom Function Space in cell D5

  • Then, press Enter. As a result, you will see a space after the second position.

Space between 2nd and 3rd numbers

  • Finally, you can do the same for all numbers with different positions.

Added space in different positions by using VBA Custom Function


πŸ’¬ Things to Remember

✎  In the first VBA code, you can add only one space. Moreover, it will only work on the C column.

✎  In the TEXT function, your format should be the same number of characters as the numbers. Otherwise, it will add a zero before them.


Download Practice Workbook

Download this exercise workbook to practice while you are reading this article.


Conclusion

To conclude, I hope this tutorial will help you to add space between numbers in Excel. Furthermore, we recommend you learn and apply all these methods to your dataset. Moreover, download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.


Related Articles

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo