Missing values from SAS
I am forced to work with an Excel sheet that was imported from SAS. It
contains the standard period "." in all cells where the data was missing. I cannot rerun the SAS to eliminate the missing data marks. Is there a way to eliminate the "." in Excel without also eliminating the decimal point in numeric cell values? |
Missing values from SAS
I found the answer myself. You just select the "Match Entire Cell Contents"
option. Thanks anyway! "Mitch" wrote: I am forced to work with an Excel sheet that was imported from SAS. It contains the standard period "." in all cells where the data was missing. I cannot rerun the SAS to eliminate the missing data marks. Is there a way to eliminate the "." in Excel without also eliminating the decimal point in numeric cell values? |
Missing values from SAS
You could write code to loop through the cells and using an if statement
test if worksheets(1).range(CellVariable).value2 = "." "Mitch" wrote in message ... I am forced to work with an Excel sheet that was imported from SAS. It contains the standard period "." in all cells where the data was missing. I cannot rerun the SAS to eliminate the missing data marks. Is there a way to eliminate the "." in Excel without also eliminating the decimal point in numeric cell values? |
Missing values from SAS
press Ctrl-H to get the Replace dialog box. Put the period in the Find box
and leave the Replace box empty Check the box for "match entire cell contents." (If it doesn't show, click the Options button to display it) "Mitch" wrote: I am forced to work with an Excel sheet that was imported from SAS. It contains the standard period "." in all cells where the data was missing. I cannot rerun the SAS to eliminate the missing data marks. Is there a way to eliminate the "." in Excel without also eliminating the decimal point in numeric cell values? |
Missing values from SAS
Ryan Jones wrote...
You could write code to loop through the cells and using an if statement test if worksheets(1).range(CellVariable).value2 = "." "Mitch" wrote in message I am forced to work with an Excel sheet that was imported from SAS. It contains the standard period "." in all cells where the data was missing. I cannot rerun the SAS to eliminate the missing data marks. Is there a way to eliminate the "." in Excel without also eliminating the decimal point in numeric cell values? VBA isn't necessary. Use Edit Replace to replace . with nothing, but make sure to check the box for 'Match entire cell contents'. That'll replace/eliminate periods that are the entire contents of their cells while leaving cells containing numbers with fractional parts as-is. |
All times are GMT +1. The time now is 11:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com