Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace first * with a space in a description | Excel Worksheet Functions | |||
Replace (') to space | Excel Worksheet Functions | |||
want Replace ALT+ENTER to SPACE | Excel Worksheet Functions | |||
How do I replace a BEL Character of 7 with a space | Excel Discussion (Misc queries) | |||
Replace a ; with a , and a space | Excel Worksheet Functions |