Need a method to convert varying french words to english within a cell

[hollerg]

I have a spreadsheet with descriptive terms in French. I would like a
search and replace method that can replace multiple the target words
within a cell, when that cell contains additional words and names that
are not to be altered.

How do I do this with substitute and a vlookup from a table of target
words and english equivalents or is there a VBA macro that will do
this?

Thank you for your time and any help you can give.

Regards
G

 

[Guest]

Have you tried Edit > Replace........

Vaya con Dios,
Chuck, CABGx3

 

[hollerg]

Find and replace requires you to type in the target and the new entry
each time rather than to access a lookup table. This would be useful
when others send new, untranslated documents to me. My technical
library of phrases can evolve as the project progresses (I am
collaborating with folks in France, and I only speak Texan)

 

[Guest]

Sounds like some good VBA (beyond my skills) could look up each word on your
list and do the Find and Replace real fast........kinda-sorta like an
"Interpreter".......

Maybe if you don't get a good response here, you could re-phrase and post
over in the Programming group.......

hth
Vaya con Dios,
Chuck, CABGx3

 

[Leo Heuser]

I have a spreadsheet with descriptive terms in French. I would like a
search and replace method that can replace multiple the target words
within a cell, when that cell contains additional words and names that
are not to be altered.

How do I do this with substitute and a vlookup from a table of target
words and english equivalents or is there a VBA macro that will do
this?

Thank you for your time and any help you can give.

Regards
G

Hi G

Here's a VBA solution with a user defined function (UDF)

Put the below code in a general module.
-----------------------------------------------------------------

Option Explicit
Option Compare Text

Function Substi(CellRange As Range, ListRange As Range) As String
'Leo Heuser, 26-8-2006
'CellRange: Cell containing word/words to be substituted
'ListRange: 2-column range with word to substitute in
'column 1 and substitute word/words in column 2
'PrefixSuffix can be increased for more characters if neccessary
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim ListRangeValue As Variant
Dim PrefixSuffix As Variant

PrefixSuffix = Array(" ", ",", ";", ":", "-", ".", "?", "!")

ListRangeValue = ListRange.Value

Substi = " " & CellRange.Value & " "

For Counter = LBound(PrefixSuffix) To UBound(PrefixSuffix)
For Counter1 = LBound(PrefixSuffix) To UBound(PrefixSuffix)
For Counter2 = 1 To UBound(ListRangeValue, 1)
Substi = Replace(Substi, PrefixSuffix(Counter) & _
ListRange(Counter2, 1) & _
PrefixSuffix(Counter1), PrefixSuffix(Counter) & _
ListRange(Counter2, 2) & _
PrefixSuffix(Counter1))
Next Counter2
Next Counter1
Next Counter

Substi = Mid(Substi, 2, Len(Substi) - 2)

End Function

--------------------------------------------------------------------------
Assuming your text in A1 and down and the substitute list named
"WordList" without quotes. When the list increases, please remember
to expand the named range with Insert > Name > Define

Use the function from the worksheet in e.g. AA1 like this:

=substi(A1,wordlist)

AA1 will now contain the text in A1 with the appropiate words replaced.

Copy down with the fill handle (the little square in the lower
corner of the cell) as far as neccessary.