D
DWalker
I have a spreadsheet that got loaded in Excel 2007 so the cells show the
following info, complete with single quotes:
'02' 'test' 'another column'
'07' 'stuff' 'more data'
I want to remove all of the single quotes but keep the leading zeros.
(I could do this with formulas, but there are 20 columns and over 32,000
rows. Replace seems like the way to go.)
So I tried to replace ' with (nothing).
1) If I do this on the whole workbook, and don't set the formats (which
are not set in the original workbook), Excel will dutifully remove the
single quotes, but also lose the leading zeros from all cells that have
strings that could be interpreted as numbers. (This example is a
simplification; there are really lots of columns, over 32000 rows, and
many columns have leading zeros that I need to keep).
2) As a test, to make sure I really knew how to use Replace with Excel
2007, I decided to highlight the first 2 rows, then set the "format" on
the "replaced" cells to "text" (to preserve the leading zeros), and did
the replace.
Oddly, Excel will then say "Excel has completed its search and made 12
replacements". But, NONE of the single quotes are gone! They are all
still there! I can do the same replace over and over, and each time,
Excel will tell me that it made 12 replacements. I don't believe it has
made ANY replacements, since the single quotes are still there.
The only way I can get the single quotes to really disappear is to
select "clear replace format" from the "replace" formatting box. Of
course, then, after the replacement, I lose the leading zeros, and
everything is stored as a number.
3) When I picked "Text" for the replacement format, the word "Preview*"
appears in the box to the left of "Format". The Help gives me no clue
what "Preview*" means.
Questions: How do I remove these single quotes and keep the zeros?
And what does "Preview*" mean?
Thanks.
David Walker
following info, complete with single quotes:
'02' 'test' 'another column'
'07' 'stuff' 'more data'
I want to remove all of the single quotes but keep the leading zeros.
(I could do this with formulas, but there are 20 columns and over 32,000
rows. Replace seems like the way to go.)
So I tried to replace ' with (nothing).
1) If I do this on the whole workbook, and don't set the formats (which
are not set in the original workbook), Excel will dutifully remove the
single quotes, but also lose the leading zeros from all cells that have
strings that could be interpreted as numbers. (This example is a
simplification; there are really lots of columns, over 32000 rows, and
many columns have leading zeros that I need to keep).
2) As a test, to make sure I really knew how to use Replace with Excel
2007, I decided to highlight the first 2 rows, then set the "format" on
the "replaced" cells to "text" (to preserve the leading zeros), and did
the replace.
Oddly, Excel will then say "Excel has completed its search and made 12
replacements". But, NONE of the single quotes are gone! They are all
still there! I can do the same replace over and over, and each time,
Excel will tell me that it made 12 replacements. I don't believe it has
made ANY replacements, since the single quotes are still there.
The only way I can get the single quotes to really disappear is to
select "clear replace format" from the "replace" formatting box. Of
course, then, after the replacement, I lose the leading zeros, and
everything is stored as a number.
3) When I picked "Text" for the replacement format, the word "Preview*"
appears in the box to the left of "Format". The Help gives me no clue
what "Preview*" means.
Questions: How do I remove these single quotes and keep the zeros?
And what does "Preview*" mean?
Thanks.
David Walker