Eliminating spaces within text cells

**dforte** · 01-08-2010, 05:08 PM

I have a formula that is dependent upon a column of cells containing text. Cells within this column randomly have an additional space (" ") following the words. With this invisible space, the formula doesn't work as intended.

Since I have an extremely long list of names, is there a way to easily remove additional spaces after words without manually going through each cell and deleting them?

**JimmyA** · 01-08-2010, 05:14 PM

Hello

If you just want to just get rid of the spaces at the end and begining and where there are multiple spaces together then you could use.......

=TRIM(A2)

If you need to get rid of all spaces in a range, the highlight the range, press ctrl + H and the put a space in the top box and leave the bottom box then press "replace all"

Hope this helps

Jim

**JBeaucaire** · 01-08-2010, 06:18 PM

Try this macro:

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

1) Select the cells that need to be fixed
2) Press Alt-F8 and select TrimSelection from the macro list

**broro183** · 01-08-2010, 07:24 PM

hi all,
Here's a slight twist on JB's version which may run slightly faster (depending on the size of the list).

Please Login or Register  to view this content.

hth
Rob

**JBeaucaire** · 01-09-2010, 05:09 AM

Heh....yours is WAY faster on a large dataset, as expected. Nice.

**shg** · 01-09-2010, 02:37 PM

Here's another way. Make sure the selection does not contain any formulas.

Please Login or Register  to view this content.

**martindwilson** · 01-09-2010, 02:49 PM

data /text to columns /fixed width/ finish
strips off leading and trailing spaces

**broro183** · 01-10-2010, 05:17 PM

hi all,

dforte,
If your problem is solved, can you please mark the thread as solved?

Thanks JB

I can't remember clearly where I first saw this technique, but I think it was one of Walkenbach's Power Programming books. Here's a link showing an example with some discussion: http://www.dailydoseofexcel.com/arch...nge-using-vba/

Shg & Martin, nice suggestions :-)

Shg, can you please talk me through how your one works?

Please Login or Register  to view this content.

I can make the concept work as a CSE entered array formulae in a range of cells in Excel but I don't understand how the logical_test meets the Help files statement of being "Any value or expression that can be evaluated to TRUE or FALSE."

Rob

**shg** · 01-10-2010, 06:42 PM

Please Login or Register  to view this content.

The Evaluate function will return an array of values if it's persuaded. It get persuaded by the ROW(range) function, which returns an array of numbers that are the row numbers of the cells being evaluated. The numbers are coerced to Boolean (all True, since they are all non-zero), which causes the Trim function to be evaluated for each.