Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
I'm having trouble working with apperently empty cells in a
spreadsheet which has data pasted from a large Word document. Some cells only look empty. I tried Clean which doesn't work completetly. I don't want to try Trim as it will replace any double spaces etc with single spaces. Using Code on one cell I get 32, telling there is a space in the cell, (other "empty" cells might have other Codes.) Is there any simple Macros I can use to delete the contents of apperently empty cells. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
Loop through the cells, loop through the characters in the cell and use the
replace command with a list of characters you want to remove. You could use the Like command in your comparisons. -- Regards, Tom Ogilvy "ian" wrote: I'm having trouble working with apperently empty cells in a spreadsheet which has data pasted from a large Word document. Some cells only look empty. I tried Clean which doesn't work completetly. I don't want to try Trim as it will replace any double spaces etc with single spaces. Using Code on one cell I get 32, telling there is a space in the cell, (other "empty" cells might have other Codes.) Is there any simple Macros I can use to delete the contents of apperently empty cells. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
Look at the CLEAN function. You mat also want to look at David McRitchies TRIMALL function:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel "ian" wrote in message s.com... | I'm having trouble working with apperently empty cells in a | spreadsheet which has data pasted from a large Word document. | | | Some cells only look empty. I tried Clean which doesn't work | completetly. I don't want to try Trim as it will replace any double | spaces etc with single spaces. | | Using Code on one cell I get 32, telling there is a space in the | cell, (other "empty" cells might have other Codes.) | | Is there any simple Macros I can use to delete the contents of | apperently empty cells. | | Thanks | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
Tom
Thanks. I was hoping to do this without specifying the characters to remove (as I don't know if I'd miss any). Just to empty any cells which look empty. Ian |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
On 7 Nov, 11:54, "Niek Otten" wrote:
Look at the CLEAN function. You mat also want to look at David McRitchies TRIMALL function: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Kind regards, Niek Otten Microsoft MVP - Excel Thanks for pointers, using WorksheetFunction.Clean and Trim seems to have worked. Ian |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove non-printing characters
Select your cells and try this:
Sub DeleteBlanks() Application.ScreenUpdating = False With Selection.SpecialCells(xlConstants) .Replace what:=" ", Replacement:="", lookat:=xlPart, searchorder:=xlByColumns, MatchCase:=True End With Application.ScreenUpdating = True End Sub HTH, JP On Nov 7, 6:35 am, ian wrote: I'm having trouble working with apperently empty cells in a spreadsheet which has data pasted from a large Word document. Some cells only look empty. I tried Clean which doesn't work completetly. I don't want to try Trim as it will replace any double spaces etc with single spaces. Using Code on one cell I get 32, telling there is a space in the cell, (other "empty" cells might have other Codes.) Is there any simple Macros I can use to delete the contents of apperently empty cells. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove all characters before a certain one | Excel Discussion (Misc queries) | |||
Remove characters | Excel Discussion (Misc queries) | |||
Remove last 10 characters | Excel Discussion (Misc queries) | |||
Remove first few characters | Excel Worksheet Functions | |||
I need to remove characters ... | Excel Discussion (Misc queries) |