Sometimes you may need to add spaces in front of the contents in a cell. If you have only a few cells, you can add spaces by moving your mouse in the cell and continuously pressing the space bar on your keyboard.
When you need to add spaces to many cells, the following 2 methods become useful.
Method 1: Ampersand (&) Formula
& (ampersand) can connect the contents in 2 or more cells into one cell. For example, you can connect the column with First Name (column A) and the column with the Last Name (column B) into a new column that has Both First and Last Name.
Formula:
= cell 1 & cell 2 (no space between)
= cell 1 &" "& cell 2 (one space between)
Using this formula, we can also add spaces in front of the cell content. For example, to add 10 spaces in front of the First Names in column A, we can modify the formula to:
=" "& A2 (please leave 10 spaces in the quotation marks)
Type the above formula in an empty cell (e.g., E2) and copy down to other cells.
Cautions:
In some situations, it may be safer to covert formula results into value by using copy and special paste.
Method 2: CONCATENATE Function
CONCATENATE is one of the text functions to join 2 or more cell into one cell. For example, you can join the column with First Name and the column with the Last Name into a new column that has Both First and Last Name.
Formula:
= CONCATENATE (cell 1, cell 2)
To add 10 spaces in front the First Names in column A, we can modify this formula as the following:
= CONCATENATE (" "&A2) (please leave 10 spaces in the quotation marks)
Type the above formula in an empty column and copy down for other cells' results.
Cautions:
From Excel 2016, the CONCATENATE function has been replaced with CONCAT function. In Excel 2016, CONCATENATE is still working, but may not be available in future versions.
