replace space

[Guest]

i am not able to replace the space in cells
ex: i have a data range of a1 to d90
in that all the cell have the spaces at the end
now i wanted to remove all those at a time

 

[Vasant Nanavati]

You would run a macro like the following with the relevant worksheet active.

Sub RemoveTralingSpaces()
Dim c As Range
For Each c In Range("A190")
c = RTrim(c)
Next
End Sub
________________________________________________________________________

 

[Guest]

What have you tried?


Select the range to be impacted

Then, if you want to remove all spaces

<edit><replace>
Find What: (type a space here)
Replace with: (leave this blank)
Click the [replace all] button

If that doesn't work...
The blank spaces may be HTML non-breaking spaces.

To remove them:
Edit>Replace
Find What: [Alt]+0160 <-Hold down [Alt]…type 0160…release [Alt]
Replace with: (leave this blank)
Click [Replace All]

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Rick Rothstein \(MVP - VB\)]

i am not able to replace the space in cells

ex: i have a data range of a1 to d90
in that all the cell have the spaces at the end
now i wanted to remove all those at a time

You could put

=TRIM(A1)

in row one of a helper column, copy down to row 90, then copy across to the
forth column over from you helper column. This will repeat all of your data,
but without the trailing spaces (or leading spaces, plus it will collapse
multiple internal spaces to single spaces also). Select the entire copy you
just made, press Ctrl+C (or click Edit/Copy on Excel's menu bar), then
select Edit/PasteSpecial from Excel's menu bar and select the Values option
from the Paste section (leave None selected from the Operation section) and
hit OK. You original data will now be changed as described above. All that
is left is to delete the helper columns.

Rick

 

[Gord Dibben]

sham

Try this macro.

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

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 Insert>Module. Paste the three sets of code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the ChangeWidthAndHeight macro by going to Tool>Macro>Macros.

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

NOTE: the code operates on whatever sheet is selected before running the macro.


Gord Dibben MS Excel MVP