Often when a list is imported from another application, you would find that there are unwanted spaces within cells.
If there are spaces in a numeric cell, excel reads it as text field. This means no calculation can be carried out on them.
For instance if a cell contains 1234 1 (space between 4 and 1), this becomes a text field and no arithmetic operation can be carried out. If you add 1 to the cell it displays #VALUE! error.
If there are spaces in a numeric cell, excel reads it as text field. This means no calculation can be carried out on them.
For instance if a cell contains 1234 1 (space between 4 and 1), this becomes a text field and no arithmetic operation can be carried out. If you add 1 to the cell it displays #VALUE! error.
We would make use of two excel functions to get rid of unwanted spaces
TRIM and SUBSTITUTE
TRIM function removes spaces from
- before the start of text eg ” 123″
- after the end of text eg “123 “
- if there are more than one spaces in the middle of the text eg. “1 23” (there are two spaces between 1 and 2, trim would remove one of them)
Cell B12 shows the use of Substitute function. The function replaces all spaces ” ” with no space “”
ps: you can find the length of a string using LEN function. The function has been used in column C. For instance C2 contains the function LEN(B2)
Useful Excel Tips 