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?

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?

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?

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?

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.