excel question: blank spaces (1 Viewer)

[Ted Lee]

hi all -

does anyone know if excel can parse a spreadsheet and remove any blank spaces at the end of any given cell?

so the cell looks like:

"text_" (where _ is a blank space)

when it should look like:

"text" (with no blank space at the end).

hope that makes sense...

 

[Glenn Overholt]

I think that makes sense. Put your cursor after the last 't', run it for a few spaces and then highlight and hit the delete button.

Glenn

 

[Tim Markley]

There's a VBA function called RTrim that will remove trailing spaces. A1=RTrim(A1) As far as I know, there isn't any kind of function that you could use without running some VBA code.

 

[Ted Lee]

thx glenn - that much i know!

the problem is these spreadsheets can be hundreds of cells large -- if not more. i want excel to automatically "search and destroy"....

vba code? ugh....

 

[Kraig Lang]

Do you have spaces in the text itself? If not, you can do a Replace all.

If you do, the trim formula will work. What you'll need to do is create a second worksheet. In the A1 cell of your new worksheet, use the formula =TRIM(Firstworksheet!A1), where firstworksheet is the name of your original spread sheet, and then copy the formula into the rest of the cells on the second worksheet.

The formulas should auto populate with the cells in direct reference from the first worksheet.

Does that make any sense at all? Sorry if I confused you.

[Edit] whoops, forgot to mention that you'll need to do a copy/paste special/values into a 3rd worksheet to remove all the formulas.

 

[Chris Hovanic]

yup... VBA is the only way that I know of to do something of that complexity! I think Tim is correct with his answer!

search http://groups.google.com/groups?group=comp&hl=en for some of your key words and you might have better luck.

 

[Ted Lee]

AWESOME KRAIG!

that worked perfectly! thanks!