Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find part text, replace all text | Excel Discussion (Misc queries) | |||
Find & replace with text | Excel Discussion (Misc queries) | |||
Replace can't find text | Excel Discussion (Misc queries) | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Find & Replace in Text Box | Excel Discussion (Misc queries) |