Replace words in cell between two same cell words - Excel

0
votes

I have a sheet filled with different word in different cell.
Ex:
Word1 Word2 Word3 Word1 Word4
Word1 Word2 Word1 Word4

I need a formula/macros to replace words between two same given words i.e.

Word1 Word2 Word3 Word1 Word4 to be replaced with
Word1 Word1 Word1 Word1 Word4


(For the above, we replaced Word2, Word3 with Word1 in the entire row where ever it is found)

Word1 Word2 Word1 Word3 to be replaced with
Word1 Word1 Word1 Word4

Please let me know if you need more details.

Thank you!

2
excelexcel-formulaexcel-2010excel-2007vba
So you want a function that loops through all cells in a sheet and replaces X with Y? Is there a limitation to area the function should iterate over? What determines which words should be replaced by what? Is it static, or based on user input? – johankr
Welcome to SO. We would need quite a few more details to answer this. Max number of columns, max gap between cells, can cells be on more than one row, etc. More importantly, we need to see what you have already done and an explanation of why it isn't working for you. Any chance you could edit the question for us? – Ambie
Anything works for me, either a function or vba script. The data can be huge i.e. 35 columns and 10,000 rows. To determine which word should be replaced between the words are 1- If we have Word2 between Word1 i.e. Word1 Word2 Word1, we need to replace Word2 with Word1 ......... 2-If we have Word2 Word3 between Word1 i.e. Word1 Word2 Word3 Word1, we need to replace Word2 Word3 with Word1......this condition need to be checked for a single row by row for the 30columns...........3-We shouldn't replace a word if it is Word1 Word4 Word1 Word1...here the series remains the same. – Coder
Ambie- Max number of columns would be 35 and there wouldn't be any blank cells.....there are only 4-6 type of words...Word1 Word2 Word3 Word4 Word5 Word6.....I was doing it manually as I had no other choice....tried using find and substitute formula and wasn't helpful. Further could you please check the previous comment as it gives you a better picture. – Coder
If you have the row Word1 Word2 Word1 Word2, should it convert to Word1 Word1 Word1 Word 2 or to Word1 Word2 Word2 Word2 – tonirush

2 Answers

0
votes

I based the code below in the two cases you provided i.e. if in between word1 there is word2 change it and if in between word1 there is word2 and word3 (I assume they come in that order and always in the two cells in between word1) change them.

The method is a bit crude due to the lack of information, but I understand you may not be able to share more for confidentiality purposes.

Private Sub sub1()
Dim rng As Range
Dim word1 As String, word2 As String, word3 As String
Dim word4 As String, word5 As String, word6 As String

Set rng = Application.InputBox("Input the range you want to substitute", "Input", Type:=8)

word1 = "WordInCell1"
word2 = "WordInCell2"
word3 = "WordInCell3"
word4 = "WordInCell4"
word5 = "WordInCell5"
word6 = "WordInCell6"

For Each c In rng
    With c
        If .Value = word1 Then
            'Check for first case
            If .Offset(0, 2).Value = word1 Then
                'Check if the word in between the two words1 is word2
                If .Offset(0, 1).Value = word2 Then
                    .Offset(0, 1).Value = word1
                End If
            'Check for second case
            ElseIf .Offset(0, 3).Value = word1 Then
                'Check if the two words in between are word2 and word3 - assuming they always come in that order
                If .Offset(0, 1).Value = word2 And .Offset(0, 2).Value = word3 Then
                    .Offset(0, 1).Value = word1
                    .Offset(0, 2).Value = word1
                End If
            Else
                'Do Nothing
            End If
        End If
    End With
Next c

End Sub
0
votes

This is a more generic solution to your problem, without the need to hard-code the strings you're looking for.

Private Sub replaceWords()
    Dim rng, currentRow As Range
    Dim currentCol, firstCol, lastCol As Integer
    Dim word1 As Variant
    Dim i, j As Integer

    Set rng = Application.InputBox("Input the range you want to substitute", "Input", Type:=8)

    'Disect the range into column numbers
    firstCol = rng.Column
    lastCol = rng.Cells.SpecialCells(xlCellTypeLastCell).Column

    'Iterate through all rows
    For Each currentRow In rng.Rows
        currentCol = firstCol 'start at the left side of the range
        While currentCol < lastCol
            'Set word1 to the value of the cell in the currenct column
            word1 = Cells(currentRow.Row, currentCol).Value

            'Check subsequent columns for the same cell
            For i = currentCol + 1 To lastCol
                If Cells(currentRow.Row, i).Value = word1 Then
                    'If same word1 is found, fill cells in between with word1
                        For j = currentCol + 1 To i - 1
                            Cells(currentRow.Row, j) = word1
                        Next j
                    'Continue search at the column where word1 was last encountered position
                    currentCol = i - 1 '-1 becaus we add 1 again at the end of the while loop
                    Exit For
                End If
            Next i
            currentCol = currentCol + 1 'go to next column
        Wend
    Next

End Sub

This will work from left to right, so "Word1 Word2 Word1 Word2" will become "Word1 Word1 Word1 Word2" rather than "Word1 Word2 Word2 Word2". It shouldn't be too hard to tweak the code if you want it the other way round.