Find and Replace Text

[Guest]

Here is my problem. I have the following text replicated hundreds of times
throughout my spreadsheet. 'Oct 2003 and 'Nov 2003 and other similar text
strings. I would like to do a find and replace on the year 2003 to 2004. When
I do this Excel changes the cell from text to date. How do I keep Excel from
doing this. Note that the cell must remain as text and that I can not just
reformat the cell as the string is a dimension name in an external OLAP cube,
that Excel References...

Thanks in Advance
Jim

 

[Frank Kabel]

Hi
try formating these cells as 'Text' before replacing them.

 

[Jim Rech]

If each "date" cell had originally been entered with an apostrophe
(literally 'October 2003) then I find a Find/Replace does not change it to a
date formatted cell (unlike a cell that is merely text formatted). So I
gather that it was not so entered (not surprisingly).

If you find that that is true also and you want to convert your date cells
to have a leading apostrophe, here's a quick and dirty macro to do that:

Sub a()
Dim Cell As Range
For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
If Cell.Value Like "* 2003" Then
Cell.Value = "'" & Cell.Value
End If
Next
End Sub


--
Jim Rech
Excel MVP
| Here is my problem. I have the following text replicated hundreds of times
| throughout my spreadsheet. 'Oct 2003 and 'Nov 2003 and other similar
text
| strings. I would like to do a find and replace on the year 2003 to 2004.
When
| I do this Excel changes the cell from text to date. How do I keep Excel
from
| doing this. Note that the cell must remain as text and that I can not just
| reformat the cell as the string is a dimension name in an external OLAP
cube,
| that Excel References...
|
| Thanks in Advance
| Jim

 

[Guest]

It generally works. My problem now revolves around there are times that I
have to do this where it is not feasable to reformat the cells as they are
intermingled with cells that I do not want to change the fromatting on. Any
bright ideas?