+ Reply to Thread
Results 1 to 3 of 3

How can I perform multiple search & replace - all data in spreadsheet?

  1. #1
    Registered User
    Join Date
    07-21-2004
    Posts
    1

    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, only the matching text within that cell.

    For example, if one line somewhere in "Column A" reads "The fox ran away from the mouse" and cell B1= fox and cell C1= dog, the resulting value for that line in "Column A" would be "The dog ran away from the mouse", as the word "fox" was replaced by the word "dog".

    Any Idea how I could accomplish this?
    Last edited by Bucyruss; 07-14-2006 at 12:14 PM.

  2. #2
    Charlie
    Guest

    RE: 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. #3
    Charlie
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1