How to add space between text in an Excel cell
By
SpreadCheaters
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 the following combination of Excel functions and operators.
In this example, we will separate the city dialing code from the phone number. The first 2 characters are the dialing code and these will be extracted using the LEFT() function. The remaining 8 characters will be extracted using the RIGHT() function. These will be joined together using the ampersand (&) with a space (“ “) in between.
1. LEFT() function: this function returns the specified number of characters from the left of the text
a. Syntax: LEFT(text,num_chars)
i. text: the text from which the characters are to be extracted
ii. num_chars: the number of characters to be extracted
2. RIGHT() function: this function returns the specified number of characters from the right of the text
a. Syntax: RIGHT(text,num_chars)
i. text: the text from which the characters are to be extracted
ii. num_chars: the number of characters to be extracted
3. Ampersand (&) operator: this joins or concatenates the extracted characters with a space (“ “) in between
Step 1 – Create the formula
– Type the formula using the cell references for the Phone number:
= LEFT(Phone number,2) & “ “ & RIGHT (Phone number, 8)
– 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
OR
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)
Note: This solution can be copied only when the length of the text is fixed and the space has to be inserted in the same place in all cells. Else the formula has to be modified for each piece of text.
