replace space
 

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

replace space
 

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

Sub RemoveTralingSpaces()
Dim c As Range
For Each c In Range("A1:D90")
c = RTrim(c)
Next
End Sub
__________________________________________________ ______________________


"sham" wrote in message
...
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

replace space
 

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:
EditReplace
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


"sham" wrote:

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

replace space
 

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

replace space
 

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 InsertModule. 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 ToolMacroMacros.

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

On Sat, 16 Jun 2007 07:42:00 -0700, sham wrote:

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