Find and Replace Text
 

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

Find and Replace Text
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Jim Thomlinson wrote:
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

Find and Replace Text
 

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
"Jim Thomlinson" wrote in message
...
| 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

Find and Replace Text
 

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?

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany


Jim Thomlinson wrote:
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