Removing spaces from cells

**cruddell001** · 04-21-2005, 03:20 PM

Is there a formula/function I can use to remove spaces from a cell? The only way I can think of is to create a do/loop VBA application, but it would a lot easier for my spreadsheet if I could avoid VBA. I tried using the trim function, but to no avail.

Note that I would much prefer a formula than just doing a search/replace, as I would like to apply the formula to new spreadsheets.

Thanks in advance.

**dfeld71** · 04-21-2005, 03:30 PM

Trim should work. If you type =trim(a1), then that will remove all surrounding spaces around the data in A1.

**swatsp0p** · 04-21-2005, 03:34 PM

Of course, no formula can change the contents of another cell. You can, however, use a helper column to modify the contents of another cell. You can then, if desired, copy the helper cells and paste special/values over top of the original cells and delete the helper column.

To find and remove a blank, use this formula in a helper column (B1 in this example), adjusting the cell reference as needed:

=REPLACE(A1,FIND(" ",A1),1,"")

"B ob" becomes "Bob"
"Hello There" becomes "HelloThere"

HTH

Bruce