Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Hello, folks!
I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Specify look in values
-- Gary''s Student - gsnu200779 "Elessvie" wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Thanks for response! But all that's available in the pull-down for Look In
is Formulas. How do I get it to show Values? Apologies for all these dumb questions! -Lynne "Gary''s Student" wrote: Specify look in values -- Gary''s Student - gsnu200779 "Elessvie" wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
I meant to add that I am able to set Look In Values in the Find pull-down,
but in the Replace With pull-down, it shows only Formulas (Values is not listed). What am I doing wrong? Any help really appreciated! -Lynne "Gary''s Student" wrote: Specify look in values -- Gary''s Student - gsnu200779 "Elessvie" wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Is this question still unanswered?
"Elessvie" wrote: I meant to add that I am able to set Look In Values in the Find pull-down, but in the Replace With pull-down, it shows only Formulas (Values is not listed). What am I doing wrong? Any help really appreciated! -Lynne "Gary''s Student" wrote: Specify look in values -- Gary''s Student - gsnu200779 "Elessvie" wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Saved from a previous post:
You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = "OneWord" AfterStr = "aDifferentWord" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Elessvie wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne -- Dave Peterson |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Dear everyone,
Thank you SO much! I will DEFINITELY try the macro and also look at the website link. Just wanted right now to thank you, even though I have no feedback on the macro at the moment. -Lynne "Dave Peterson" wrote: Saved from a previous post: You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = "OneWord" AfterStr = "aDifferentWord" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Elessvie wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne -- Dave Peterson |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Hi, Dave,
This macro works beautifully! There are MANY ways we can use it here. Thanks again, -Lynne "Dave Peterson" wrote: Saved from a previous post: You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = "OneWord" AfterStr = "aDifferentWord" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Elessvie wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne -- Dave Peterson |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
But what a pain, huh?????
Elessvie wrote: Hi, Dave, This macro works beautifully! There are MANY ways we can use it here. Thanks again, -Lynne "Dave Peterson" wrote: Saved from a previous post: You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = "OneWord" AfterStr = "aDifferentWord" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Elessvie wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Find and Replace Question
Dave, I'm so happy to have something like this, that I can't complain!
-Lynne "Dave Peterson" wrote: But what a pain, huh????? Elessvie wrote: Hi, Dave, This macro works beautifully! There are MANY ways we can use it here. Thanks again, -Lynne "Dave Peterson" wrote: Saved from a previous post: You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = "OneWord" AfterStr = "aDifferentWord" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Elessvie wrote: Hello, folks! I'm trying to find and replace a word with another in a column containing text. I'm using Ctrl-H, Find = reciept (for example), Replace with = receipt. This locates a cell containing the word, but when I press Replace, Excel displays the error message "Formula is too long." I've tried formatting the column as General and as Text and get the error message with both. I've searched Excel Help and this discussion group, but haven't been able to find this specific issue addressed, so I'm thinking it's probably something really obvious! Thank you for any help. -Lynne -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Find and Replace Question | Excel Discussion (Misc queries) | |||
Advanced Find and Replace Question | Excel Worksheet Functions | |||
Find & replace question.....I believe | Excel Discussion (Misc queries) | |||
Simple Search and Replace Question | Excel Discussion (Misc queries) | |||
Find/Replace Question | Excel Discussion (Misc queries) |