Excel 2010: Removing spaces from excel

Tricks:

1. Copy the column or row you need to remove the space from
2. Open a .csv file and then paste into destination format. It will remove all the formatting and unwanted spaces.

On a computer, a space between words is not a blank area but a character, and, believe it or not, there is more than one type of space character.

One space character, commonly used in web pages, which TRIM will not remove is the non-breaking space (&nbsp).

1 2 3 4 5

A Data BD 122 ="XY"&CHAR(7)&"453"  BD    122 MN987 Formula Description (Result) =TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112) =CLEAN(A3) Removes the nonprinting BEL character (ASCII value of 7) from the string value created by the expression ="XY"&CHAR(7)&"453" (XY453) =TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32))) Replaces each nonbreaking space character (Unicode value of 160) with a space character (ASCII value of 32) by using the SUBSTITUTE function, and then removes the leading and multiple embedded spaces from the string " BD   122" (BD 112) =CLEAN(SUBSTITUTE(A5,CHAR(127),CHAR(7))) Replaces the nonprinting DEL character (ASCII value of 127) with a BEL character (ASCII value of 7) by using the SUBSTITUTE function, and then removes the BEL character from the string "MN987" (MN987)

Spot the Differences

Working with Excel data can be like one of those “Spot the Difference” puzzles. What’s different between list A and list B? If you’re lucky, the differences are obvious, like the forward slash in column B, and no leading slash in column E. Other times, it’s tougher to find the differences.

A common problem is items with leading or trailing spaces. You can’t see them on the screen, but after you’ve encountered them a few times, you learn to check for them. The LEN function is a great help if you suspect there are hidden space characters.