Removing trailing spaces in a spreadsheet
 

Hello,
I have a spreadsheet full of entries imported from a database, chock-full of
trailing spaces of different lengths. Is there any simple way to get rid of
them all at once?

Removing trailing spaces in a spreadsheet
 

TRIM() removes all multiple and trailing spaces

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Kamran" wrote in message ...
| Hello,
| I have a spreadsheet full of entries imported from a database, chock-full of
| trailing spaces of different lengths. Is there any simple way to get rid of
| them all at once?

Removing trailing spaces in a spreadsheet
 

Sorry, I'm not that advanced -- where do I put that?

Removing trailing spaces in a spreadsheet
 

If you want to apply it to the entire spreadsheet try this...

On a clean spreadsheet select cell A1.

Type the following into the cell: "=Trim("

Then switch back to the original sheet and click on Cell A1 and hit Enter.

Now you have an entry in cell A1 of your new sheet that does not have any
trailing spaces. Copy and paste the cell into the new sheet over the same
range as the original sheet. You should now have a sheet that looks the same
as your original sheet.

One more step... Click the gray box on the top left side of the new sheet
to select the entire sheet. Use edit... copy to copy the contents of the
sheet to the clipboard. Then, use edit... paste special... select values
from the pop-up box, click on O.K. to paste the cells back into the sheet as
plain text instead of formulas.

Regards...







"Kamran" wrote:

Sorry, I'm not that advanced -- where do I put that?

Removing trailing spaces in a spreadsheet
 

Okay, I did the first part, but that's only cell A1. The second part just
sounds like a Paste Special Values operation, but I don't see how to
incorporate the TRIM function into it. So I still need help with applying it
to the whole sheet.


"ChristopherTri" wrote:

If you want to apply it to the entire spreadsheet try this...

On a clean spreadsheet select cell A1.

Type the following into the cell: "=Trim("

Then switch back to the original sheet and click on Cell A1 and hit Enter.

Now you have an entry in cell A1 of your new sheet that does not have any
trailing spaces. Copy and paste the cell into the new sheet over the same
range as the original sheet. You should now have a sheet that looks the same
as your original sheet.

One more step... Click the gray box on the top left side of the new sheet
to select the entire sheet. Use edit... copy to copy the contents of the
sheet to the clipboard. Then, use edit... paste special... select values
from the pop-up box, click on O.K. to paste the cells back into the sheet as
plain text instead of formulas.

Regards...







"Kamran" wrote:

Sorry, I'm not that advanced -- where do I put that?

Removing trailing spaces in a spreadsheet
 

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Fri, 8 Dec 2006 11:30:01 -0800, Kamran
wrote:

Okay, I did the first part, but that's only cell A1. The second part just
sounds like a Paste Special Values operation, but I don't see how to
incorporate the TRIM function into it. So I still need help with applying it
to the whole sheet.


"ChristopherTri" wrote:

If you want to apply it to the entire spreadsheet try this...

On a clean spreadsheet select cell A1.

Type the following into the cell: "=Trim("

Then switch back to the original sheet and click on Cell A1 and hit Enter.

Now you have an entry in cell A1 of your new sheet that does not have any
trailing spaces. Copy and paste the cell into the new sheet over the same
range as the original sheet. You should now have a sheet that looks the same
as your original sheet.

One more step... Click the gray box on the top left side of the new sheet
to select the entire sheet. Use edit... copy to copy the contents of the
sheet to the clipboard. Then, use edit... paste special... select values
from the pop-up box, click on O.K. to paste the cells back into the sheet as
plain text instead of formulas.

Regards...







"Kamran" wrote:

Sorry, I'm not that advanced -- where do I put that?

Removing trailing spaces in a spreadsheet
 

Perfect. Thanks for the responses to everyone.


"Gord Dibben" wrote:

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Fri, 8 Dec 2006 11:30:01 -0800, Kamran
wrote:

Okay, I did the first part, but that's only cell A1. The second part just
sounds like a Paste Special Values operation, but I don't see how to
incorporate the TRIM function into it. So I still need help with applying it
to the whole sheet.


"ChristopherTri" wrote:

If you want to apply it to the entire spreadsheet try this...

On a clean spreadsheet select cell A1.

Type the following into the cell: "=Trim("

Then switch back to the original sheet and click on Cell A1 and hit Enter.

Now you have an entry in cell A1 of your new sheet that does not have any
trailing spaces. Copy and paste the cell into the new sheet over the same
range as the original sheet. You should now have a sheet that looks the same
as your original sheet.

One more step... Click the gray box on the top left side of the new sheet
to select the entire sheet. Use edit... copy to copy the contents of the
sheet to the clipboard. Then, use edit... paste special... select values
from the pop-up box, click on O.K. to paste the cells back into the sheet as
plain text instead of formulas.

Regards...







"Kamran" wrote:

Sorry, I'm not that advanced -- where do I put that?