Large-Scale Find/Replace In Word With Excel Lists

[alexposadas]

I'm praying for help to accomplish something (VBA/Excel related).
Here's a brief description, so ya'll can try to assist me or refer me to another brother:

Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell).
Cell B1 contains a paragraph of English words (They are Bible verses in English, so sometimes more than 254 characters long in each cell).
Column C contains a list of single Hebrew words.
Column D contains the Strong's code for each of those Hebrew words.
Column E contains a list of single English words.
Column F contains the Strong's code for each of those English words.
Finally, Column G contains a list of single words that will be replacing the English words with IF all criteria is met.
(these words are transliterated single words).

So, this is what I'm trying to do:

IF a Hebrew word within the paragraph of cell A1, is the same as a Hebrew word found in the list in Column C,
AND an English word within the paragraph of cell B1, is the same as an English word found in the list in Column E.
AND the Strong's code for this word is the same in (both) Column D and Column F,
THEN create a new paragraph (Bible verse) in a (new) Column H, which will be a combination: Rendering the existing English words from B1 that did not meet this criteria, but rendering the transliterated words from Column G when all criteria is met.

I'm not sure Excel alone will support this task because of the 254 character issue, but since I don't know VBA, I figure getting help in Excel will be easier.

I've attached a sample Excel file to show what I've put together so ya'll can see it laid out.

Thanks for trying to help and especially for helping me pray for the solution to this.
Alex

[macropod]

Cross-posted at: http://www.techsupportforum.com/forums/f57/replacing-words-in-paragraphs-within-cells-if-it-meets-all-criteria-described-729401.html
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[alexposadas]

thank you Paul

[macropod]

Hi Alex,

I'm not sure what you're trying to achieve here, let alone how you would propose getting the results you require.

It seems you're after the Hebrew transliteration and/or Strong's numbers for the New Testament. Given that any Hebrew New Testament in existence will probably be a translation from the Greek (the old Aramaic translations are an unlikely possibility), the only valid Strong's numbers would be the Greek ones, which you already have.

Furthermore, given that: (a) the English and Hebrew New Testament translations have completely different word orders from each other and the Greek text; (b) translators often add or delete words where appropriate to the translation; and (c) you're basing your English text on the arcane KJV rather than a modern translation that uses modern English based on the best original texts, I doubt one could programmatically take the individual words from your English text and find the correct Hebrew transliteration/Strong's number for it. I may be wrong about that, but it would help if you provided enough data and the sample output for at least a few complete verses to demonstrate your goal.

[alexposadas]

(I tried to send you this as a private message but I'm not allowed until I have at least 10 posts)

Shalom Paul,

I also believe, as you do, that the pe***ta is probably a translation :)
I also agree that the NT was probably written originally in Greek.
And here's where we may not exactly agree but I'll still share: That I believe in the possibility that nevertheless, the words of the NT indeed still need to be received with Hebrew thought-process, which would make sense, since we have record that Mashiach read and taught from the Tanach. In other words, we can throw out my theory if we can prove that Mashiach read and taught the Scriptures out of the Septuagint! (which is remotely possible but pretty unlikely).

So here's my secret (which is no longer a secret because I'm about to tell you) :clap:
I want to match every word I can match between these 2 texts:
The NT Delitzsch (a translation from Greek to Hebrew),
The Westminster Leningrad Codex of the OT. (for this, I have parsed the words).
And thus create a Hebrew NT with "1st choice" Hebrew Strong's.
But my goal is not to create an inspired Hebrew NT. So that alone will not be sensible. I also want to preserve the existing Greek words along with their Strong's codes and their lexicon entries, and in addition to that, I want to reference the LXX (Septuagint) related words as well (I want all this in the footnote, sidenote, etc). Thus, my result will be: I guess we can call it something like "my own study Bible".

At this point, I have macro solutions that'll do almost everything I want to do, except that they're just not suitable for such large amounts of text that need to be processed. In order to accomplish my idea, it will be necessary to share it with someone that knows how to handle large Find/Replace procedures. In other words: Someone who will have the knowledge to write more efficient code for such tasks.

From your post, I can tell you seem to you know a lot of what I'm talking about.
If that's the case and you are also interested in the subject, it would be nice to meet you via Skype audio,
so I can layout my idea to you further.

Shalom,
Alex (Aharon ben Yaakov)

[macropod]

If you want a better Find/Replace engine, I'd suggest working in Word. Your lookup lists can remain in Excel, but having the NT text itself in a Word document would probably be far more efficient.

[alexposadas]

Hi Paul,
Well, I do have a Word macro that Finds/Replaces as you say, from an external "mylist.xlsx" file looking up from Column A and replacing with what's on Column B.
But what I was trying to say is that it takes forever! :(
Some say it's better to parse my text first (word for word), others say it's better to put everything I want into a database and then output from there.
I just finished skyping with a programmer in Central America and he said he would do everything first in sql and then output to Excel, and THEN output again from there to Word so I can format my pages etc etc etc.
I don't know, Paul. If I come up with a better idea, I'll run it by you, since I know you'll give me good advice.
Shalom,
Alex

[macropod]

Since all your posts in this thread have until now only mentioned Excel, there's no way anyone could know you've been using Word. If the code 'takes forever', that suggests it could do with some re-working. The following macro allows you to use an Excel Workbook for a large-scale Find/Replace operation in Word.

Sub BulkFindReplace()
Application.ScreenUpdating = True
Dim xlApp As Object, xlWkBk As Object, iDataRow As Long, i As Long
Dim xlFList As String, xlRList As String, StrWkBkNm As String, StrWkSht As String
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkSht = "Sheet1"
If Dir(StrWkBkNm) = "" Then
MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
Exit Sub
End If
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel.", vbExclamation
Exit Sub
End If
With xlApp
'Hide our Excel session
.Visible = False
' open the file
Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True)
If xlWkBk Is Nothing Then
MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation
.Quit
Exit Sub
End If
' Process the workbook.
With xlWkBk.Worksheets(StrWkSht)
' Find the last-used row in column A.
' Add 1 to get the next row for data-entry.
iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp
' Output the captured data.
For i = 1 To iDataRow
' Skip over empty fields to preserve the underlying cell contents.
If Trim(.Range("A" & i)) <> vbNullString Then
xlFList = xlFList & "|" & Trim(.Range("A" & i))
xlRList = xlRList & "|" & Trim(.Range("B" & i))
End If
Next
End With
xlWkBk.Close False
.Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
'Process each word from the F/R List
For i = 1 To UBound(Split(xlFList, "|"))
With ActiveDocument
With .Range.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWholeWord = True
.MatchCase = True
.Wrap = wdFindStop
.Text = Split(xlFList, "|")(i)
.Replacement.Text = Split(xlRList, "|")(i)
.Execute Replace:=wdReplaceAll
End With
.UndoClear
End With
Next
Application.ScreenUpdating = True
End Sub
The above code is quite efficient. Still, the problem remains as to how to know which English word corresponds with both a given Greek word and its Hebrew counterpart when the word order and semantic range in all three is unrelated.

[macropod]

FWIW, if you're keen to get into the Jewish thought processes behind the NT, you could do worse than get yourself a copy of:
Stern, D. (1989). Jewish New Testament. Clarksville: Jewish New Testament Publications.
and:
Stern, D. (1992). Jewish New Testament Commentary. Clarksville: Jewish New Testament Publications.
Even Stern, though, misses a few, such as Galatians 2:15-16:
(15) Ἡμεῖς φύσει Ἰουδαῖοι καὶ οὐκ ἐξ ἐθνῶν ἁμαρτωλοί· (16) εἰδότες [δὲ] ὅτι οὐ δικαιοῦται ἄνθρωπος ἐξ ἔργων νόμου ἐὰν μὴ διὰ πίστεως Ἰησοῦ Χριστοῦ, καὶ ἡμεῖς εἰς Χριστὸν Ἰησοῦν ἐπιστεύσαμεν, ἵνα δικαιωθῶμεν ἐκ πίστεως Χριστοῦ καὶ οὐκ ἐξ ἔργων νόμου, ὅτι ἐξ ἔργων νόμου οὐ δικαιωθήσεται πᾶσα σάρξ.
which would be better translated as:
(15) We ourselves, natural‐born Jews ‐ not even from ‘Gentile sinners’ ‐ (16) knowing [nevertheless] that a man of Torah compliance is not justified except through Jesus Christ’s faithfulness and one’s trust in Him, even we trusted in Christ Jesus so as to be justified by trust in Christ and not by Torah compliance, because by Torah compliance: ‘all flesh will not be justified’.

[snb]

or


Sub M_snb()
on error goto 1

with getobject("C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls")
sn=.sheets("sheet1").cells(1).currentregion.resize(,2)
.close 0
end with

c00=activedocument.content
For j = 2 To UBound(sn)
c00 =replace(c00,sn(j,1),sn(j,2))
Next

activedocument.content=c00

1
End Sub

[alexposadas]

shalom Paul, I'm trying to PM you to get into more detail regarding my work but it's not letting me till I have at least 10 posts. Can we talk via Skype audio?

[macropod]

Skype's not viable for me ATM - travelling.

[alexposadas]

I see, no problem, perhaps later on, so I can share further details. Basically help deciding the best way to go on how to handle NT Hebrew words. Shalom. My facebook is located here: https://www.facebook.com/smuglovsky

[SamT]

Alex,

I am marking this thread solved, and I am changing the Title to "Large-Scale Find/Replace In Word With Excel Lists"

[macropod]

or


Sub M_snb()
...
End Sub
That code would completely wipe out a document's formatting! Not terribly useful IMHO...

[snb]

@macropod

I really can't tell, the OP didn't provide any document.

[macropod]

One doesn't need the OP's document to understand what the code would do to the formatting of any document...

[macropod]

Hi alexposadas,

You might be interested in: http://www.hebrew4christians.com/Online_Store/Books/Parallel_NT/parallel_nt.html. No sense in re-inventing the wheel.