Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I perform multiple search & replace - all data in spreadsheet?
I have a spreadsheet with three columns. Column A contains a couple thousand lines of text. Column B contains a list of values to search for in Column A Column C contains a list of values to replace found values I want to search the entire column a for values in "column B" and replace with corresponding value in "column C". for example, search column A for values in B1 and replace all matches with the value in C1, and continue until all lines of text in column A have been examined. Then do the same for the old/new pair B2/C2, then B3/C3, etc right down the list. Column A may contain multiple instances of matches for any given value in column B. Note: This action does not replace the entire column a cell value. Example: Column a Column B Column C -------------------------- --------- --------- The wild fox jumps fox dog The cat ate the mouse cat owl The bear saw the fox bear deer The cat ran from the fox The piano played Should produce this output: The wild dog jumps The owl ate the mouse The deer saw the dog The owl ran from the dog The piano played Any Idea how I could accomplish this? -- Bucyruss ------------------------------------------------------------------------ Bucyruss's Profile: http://www.excelforum.com/member.php...o&userid=12070 View this thread: http://www.excelforum.com/showthread...hreadid=561521 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I perform multiple search & replace - all data in spreadsh
This one is not as simple as it seems. First of all, I provided a
"ReplaceAll" function that replaces multiple occurrences of words ("Replace" does not); and second, I presume you are replacing whole words i.e. Replace("Waterfowl", "owl", "dog") becomes "Waterfdog" which is not what you want, so I added a section to append spaces. i.e., Replace(..., " owl ", " dog ") Public Function ReplaceAll(txt As String, FindAll As String, ReplaceWith As String) As String ' ' the Replace function only makes one pass and the resulting replacement may produce ' another substring requiring replacement - ReplaceAll will loop until all substrings ' have been replaced ' ReplaceAll = txt If FindAll < "" And FindAll < ReplaceWith Then Do While InStr(ReplaceAll, FindAll) 0 ReplaceAll = Replace(ReplaceAll, FindAll, ReplaceWith) Loop End If ' End Function Dim Cell As Range Dim iRow As Long Dim LastRowInColA As Long Dim LastRowInColB As Long Dim FindStr() As String Dim ReplaceWith() As String LastRowInColA = ? LastRowInColB = ? FindReplace = Range(Cells(1, 2), Cells(LastRowInColB, 3)).Value ReDim FindStr(LastRowInColB) ReDim ReplaceWith(LastRowInColB) ' ' must append leading and trailing spaces to prevent ' Replace("Waterfowl", "owl", "dog") -- "Waterfdog" ' For iRow = 1 To LastRowInColB FindStr(iRow) = " " & FindReplace(iRow, 1) & " " ReplaceWith(iRow) = " " & FindReplace(iRow, 2) & " " Next iRow For Each Cell In Range(Cells(1, 1), Cells(LastRowInColA, 1)) For iRow = 1 To LastRowInColB Cell.Value = Trim(ReplaceAll(" " & Cell.Value & " ", FindStr(iRow), ReplaceWith(iRow))) Next iRow Next Cell "Bucyruss" wrote: I have a spreadsheet with three columns. Column A contains a couple thousand lines of text. Column B contains a list of values to search for in Column A Column C contains a list of values to replace found values I want to search the entire column a for values in "column B" and replace with corresponding value in "column C". for example, search column A for values in B1 and replace all matches with the value in C1, and continue until all lines of text in column A have been examined. Then do the same for the old/new pair B2/C2, then B3/C3, etc right down the list. Column A may contain multiple instances of matches for any given value in column B. Note: This action does not replace the entire column a cell value. Example: Column a Column B Column C -------------------------- --------- --------- The wild fox jumps fox dog The cat ate the mouse cat owl The bear saw the fox bear deer The cat ran from the fox The piano played Should produce this output: The wild dog jumps The owl ate the mouse The deer saw the dog The owl ran from the dog The piano played Any Idea how I could accomplish this? -- Bucyruss ------------------------------------------------------------------------ Bucyruss's Profile: http://www.excelforum.com/member.php...o&userid=12070 View this thread: http://www.excelforum.com/showthread...hreadid=561521 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I perform multiple search & replace - all data in spre
Let me retract the part about the ReplaceAll function. The Replace function
should work fine in your case. I use the ReplaceAll function for replacing multiple occurrences of the same character! For example, to compress out all multiple spaces into single spaces, "this is a test" -- "this is a test", the Replace function may leave behind two or more consecutive spaces. Compressed_string = Replace(OldStr, " ", " ") ' two spaces with one space. If "Replace" finds three spaces in a row, two will be replaced with one still leaving two behind. That's when the ReplaceAll function is usefull Compressed_string = ReplaceAll(OldStr, " ", " ") ' two spaces with one space. "Charlie" wrote: This one is not as simple as it seems. First of all, I provided a "ReplaceAll" function that replaces multiple occurrences of words ("Replace" does not); and second, I presume you are replacing whole words i.e. Replace("Waterfowl", "owl", "dog") becomes "Waterfdog" which is not what you want, so I added a section to append spaces. i.e., Replace(..., " owl ", " dog ") Public Function ReplaceAll(txt As String, FindAll As String, ReplaceWith As String) As String ' ' the Replace function only makes one pass and the resulting replacement may produce ' another substring requiring replacement - ReplaceAll will loop until all substrings ' have been replaced ' ReplaceAll = txt If FindAll < "" And FindAll < ReplaceWith Then Do While InStr(ReplaceAll, FindAll) 0 ReplaceAll = Replace(ReplaceAll, FindAll, ReplaceWith) Loop End If ' End Function Dim Cell As Range Dim iRow As Long Dim LastRowInColA As Long Dim LastRowInColB As Long Dim FindStr() As String Dim ReplaceWith() As String LastRowInColA = ? LastRowInColB = ? FindReplace = Range(Cells(1, 2), Cells(LastRowInColB, 3)).Value ReDim FindStr(LastRowInColB) ReDim ReplaceWith(LastRowInColB) ' ' must append leading and trailing spaces to prevent ' Replace("Waterfowl", "owl", "dog") -- "Waterfdog" ' For iRow = 1 To LastRowInColB FindStr(iRow) = " " & FindReplace(iRow, 1) & " " ReplaceWith(iRow) = " " & FindReplace(iRow, 2) & " " Next iRow For Each Cell In Range(Cells(1, 1), Cells(LastRowInColA, 1)) For iRow = 1 To LastRowInColB Cell.Value = Trim(ReplaceAll(" " & Cell.Value & " ", FindStr(iRow), ReplaceWith(iRow))) Next iRow Next Cell "Bucyruss" wrote: I have a spreadsheet with three columns. Column A contains a couple thousand lines of text. Column B contains a list of values to search for in Column A Column C contains a list of values to replace found values I want to search the entire column a for values in "column B" and replace with corresponding value in "column C". for example, search column A for values in B1 and replace all matches with the value in C1, and continue until all lines of text in column A have been examined. Then do the same for the old/new pair B2/C2, then B3/C3, etc right down the list. Column A may contain multiple instances of matches for any given value in column B. Note: This action does not replace the entire column a cell value. Example: Column a Column B Column C -------------------------- --------- --------- The wild fox jumps fox dog The cat ate the mouse cat owl The bear saw the fox bear deer The cat ran from the fox The piano played Should produce this output: The wild dog jumps The owl ate the mouse The deer saw the dog The owl ran from the dog The piano played Any Idea how I could accomplish this? -- Bucyruss ------------------------------------------------------------------------ Bucyruss's Profile: http://www.excelforum.com/member.php...o&userid=12070 View this thread: http://www.excelforum.com/showthread...hreadid=561521 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! How do I search for multiple criteria in a large spreadsheet | Excel Discussion (Misc queries) | |||
How to perform Search and Replace on "Tilde", CHAR(126), ~ | Excel Worksheet Functions | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) | |||
how do i do a search in a spreadsheet thas has multiple worksheet | Excel Discussion (Misc queries) | |||
Excel VBA - Search And Replace Cell data | Excel Programming |