Find and replace function

[DutchGirl]

We have an excel sheet with article numbers and descriptions in three languages. The list is about 1000 rows long and is expanding every week. We are about to add two more languages and this, together with the fact that the list is still expanding, made us looking for a way to translate the list more efficiently. There are words and phrases that return in the list several times (as part of sentences), so when we would have to translate those only once, it would save a lot of time.

I started of with an extra sheet with translations and a substitute function. This worked, but the list with translations will be about 200 rows, and you can't nest a substitute function more than 64 times.

I have never made VBA code myself but it became clear we cannot solve this problem with the existing functions.

Then I found this VBA-function, which works very well, but doesn't match whole words and phrases (so if a word is part of another word, it changes that too).
How To Find And Replace Multiple Text Strings Within A Text String | How To Excel

Then I found a thread on this forum, but I would prefer a function over a macro which you have to run. There are multiple people working with the file, and we never use VBA, so I believe using a function will be more clear for all users.
VBA: Find and Replace Exact Match Words

(I did search this forum for a better alternative before posting this question, but couldn't find it. If I overlooked threads that already contain a solution for our problem, then sorry for this post, and please let me know where I find it!)

 

[davesexcel]

Please supply a sample of what you are asking.

 

[DutchGirl]

I couldn't find a way to upload a shortened version of the excel file... (so that you can also see the code)

 

[Akuini]

Then I found this VBA-function, which works very well, but doesn't match whole words and phrases (so if a word is part of another word, it changes that too).

You need to use Regex utilzing word boundary.
Here's an example:

Sub DutchGirl()

Dim strPattern As String
Dim strReplace As String
Dim regEx As Object
Dim strInput As String
Dim va, vb

va = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 2)  'FIND in col A &  REPLACE WITH in col B

vb = Range("D2", Cells(Rows.Count, "D").End(xlUp))  'FIND IN: col D
 
     Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = True
            .ignorecase = True
            
        
            For i = LBound(va) To UBound(va)
                .Pattern = "\b" & va(i, 1) & "\b"
                strReplace = va(i, 2)
                  
                  For j = LBound(vb) To UBound(vb)
                     strInput = vb(j, 1)
            
                     If regEx.test(strInput) Then
                        vb(j, 1) = regEx.Replace(vb(j, 1), strReplace)
                     End If
                 Next
            Next
            
        End With
        
'RESULT IN COL e
Range("E2").Resize(UBound(vb), 1).Value = vb

End Sub

Example:

Book1
	A	B	C	D	E
1	FIND	REPLACE WITH		FIND IN:	RESULT
2	car	#car		It's a car	It's a #car
3	my book	#my book		My card is this	My card is this
4				My book is there	#my book is there
5				My books are gone	My books are gone
6				My book is there. My book is new.	#my book is there. #my book is new.
Sheet1

 

[Akuini]

To use Regular Expressions you need to add reference to "Microsoft VBScript Regular Expressions 5.5":
In Visual Basic Editor menu, select Tools –> References, then select Microsoft VBScript Regular Expressions 5.5, then click OK.

 

[DutchGirl]

Now my lack of vba knowledge is becoming obvious, but how do I turn a macro you have to run (=sub??) into a function you can use in a cell (for example =REPLACETEXT() This is being done in the first example I gave. I copy pasted this of this website:

How To Find And Replace Multiple Text Strings Within A Text String | How To Excel

Function REPLACETEXTS(strInput As String, rngFind As Range, rngReplace As Range) As String

Dim strTemp As String
Dim strFind As String
Dim strReplace As String

Dim cellFind As Range

Dim lngColFind As Long
Dim lngRowFind As Long
Dim lngRowReplace As Long
Dim lngColReplace As Long

lngColFind = rngFind.Columns.Count
lngRowFind = rngFind.Rows.Count
lngColReplace = rngFind.Columns.Count
lngRowReplace = rngFind.Rows.Count

strTemp = strInput

If Not ((lngColFind = lngColReplace) And (lngRowFind = lngRowReplace)) Then
    REPLACETEXTS = CVErr(xlErrNA)
    Exit Function
End If

For Each cellFind In rngFind

    strFind = cellFind.Value
    strReplace = rngReplace(cellFind.Row - rngFind.Row + 1, cellFind.Column - rngFind.Column + 1).Value
    strTemp = Replace(strTemp, strFind, strReplace)

Next cellFind

REPLACETEXTS = strTemp

End Function

 

[Akuini]

Now my lack of vba knowledge is becoming obvious, but how do I turn a macro you have to run (=sub??) into a function you can use in a cell (for example =REPLACETEXT() This is being done in the first example I gave.

Try this:

Function to_Replace(strInput As String, rngFind As Range, rngReplace As Range) As String
'To use Regular Expressions you need to add reference to "Microsoft VBScript Regular Expressions 5.5":
'In Visual Basic Editor menu, select Tools –> References, then select Microsoft VBScript Regular Expressions 5.5, then click OK.

Dim strPattern As String
Dim strReplace As String
Dim regEx As Object
Dim i As Long
Dim tx As String
Dim va, vb

va = rngFind.Value
vb = rngReplace.Value
tx = strInput
 
     Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = True
            .ignorecase = True
            
        
            For i = LBound(va) To UBound(va)
                    .Pattern = "\b" & va(i, 1) & "\b"
                     If regEx.test(strInput) Then
                         tx = regEx.Replace(strInput, vb(i, 1))
                     End If
            Next
            
        End With
        
to_Replace = tx

End Function

Example:

Book1
	A	B	C	D	E
1	FIND	REPLACE WITH		TEXT	RESULT
2	car	#car		It's a car	It's a #car
3	my book	#my book		My card is this	My card is this
4				My book is there	#my book is there
5				My books are gone	My books are gone
6				My book is there. My book is new.	#my book is there. #my book is new.
Sheet2

Cell Formulas
Range	Formula
E2:E6	E2	=to_Replace(D2,$A$2:$A$3,$B$2:$B$3)

 

[Akuini]

sorry, the code in post #7 is flawed, use this one instead:

Function to_Replace(strInput As String, rngFind As Range, rngReplace As Range) As String
'To use Regular Expressions you need to add reference to "Microsoft VBScript Regular Expressions 5.5":
'In Visual Basic Editor menu, select Tools –> References, then select Microsoft VBScript Regular Expressions 5.5, then click OK.

Dim strPattern As String
Dim strReplace As String
Dim regEx As Object
Dim i As Long
Dim tx As String
Dim va, vb

va = rngFind.Value
vb = rngReplace.Value
tx = strInput
 
     Set regEx = CreateObject("VBScript.RegExp")
        With regEx
            .Global = True
            .MultiLine = True
            .ignorecase = True
            
        
            For i = LBound(va) To UBound(va)
                    .Pattern = "\b" & va(i, 1) & "\b"
                     If regEx.test(tx) Then
                         tx = regEx.Replace(tx, vb(i, 1))
                     End If
            Next
            
        End With
        
to_Replace = tx

End Function

 

[DutchGirl]

Thank you for your response! I've tried the function but get an error:

 

[Akuini]

I don't understand your data. Which part is the text input? and which part is the find-replace list?
Please check dummy data in post #4, if you understand it you can adapt your formula to suit your data lay out.
Could you post an example with XL2BB tool?