How to add a space in an Excel cell

You can watch a video tutorial here.

Many applications offer you the option of exporting data to Excel. When working with data that has been exported from another application you will frequently need to clean and format the data so that you can proceed to analyze or summarize it. One such operation could be to add a space in a cell. This can be done using a combination of Excel functions and operators. In this example, we will separate the city dialing code from the phone number. 

Note: This solution can be used only when the length of the text is fixed and the space has to be inserted in the same place in all cells.

Step 1 – Create the formula

– Type the formula using the cell references for the Phone number:
= LEFT(Phone number,2) & “ “ & RIGHT (Phone number, 8)
The LEFT() function returns the specified number of characters from the left of the text (in this case, 2)
The RIGHT() function returns the specified number of characters from the right of the text (in this case, 8)
The ampersands (&) join or concatenate the extracted characters with a space (“ “) in between
– Press Enter

Step 2 – Copy the formula

– Space is inserted between the city dialing code and the phone number
– Using the fill handle from the first cell, drag the formula to the remaining cells
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)