Insert Parenthesis before and after each word in an excel column.

[Singh_Edm]

Hi there,

I have an 90,000 row MS Excel file where, for one of the columns, I am looking for help to draft a code which will insert opening parenthesis before a word and closing parenthesis after a word. Parentheses means round brackets ()

As second part of the project, I have a dictionary MS Word file where each word is bold and its meaning is given after a tab. After the meaning, there is paragraph break. I want MS Excel to pick a word from the dictionary file, look for it in the column (where the parentheses work was just done) and insert the meaning after the parentheses enclosing the word.

For e.g.
ORIGINAL ENTRY IN EXCEL
It is sunny
AFTER EXECUTION OF 'PARENTHESES INSERTION' CODE
(It) (is) (sunny)
AFTER EXECUTION OF 'IMPORT MEANINGS AND INSERT' CODE
(It) used to represent animate thing understood (is) 3rd person singular present indicative of be (sunny) abounding in sunshine

If it is not possible to import directly, is there a way to make a table in MS Word with the words listed in one column and meanings in a second column. I can then copy paste this into excel. This table can then be used to achieve 'insertion of meanings'.

I've been helped a lot by this forum in June this year and I hope the best again. I am not a programmer, I just know how to execute Macros but not write them.

 

[L. Howard]

post deleted

 

[L. Howard]

delete post

 

[L. Howard]

(Having trouble with posting, sorry)

Give this a try, assumes your 90,000 entries are in column A and puts the finished product with each word in ( )'s in column B.

I don't know how to add the definition's.

Regards,
Howard

Sub TesterParAdd()

  Dim lRowCount&
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row
  
  With Range("B1").Resize(lRowCount)
    .Formula = "=""("" & A1 & "")""": .Value = .Value
    .Replace What:=" ", Replacement:=") (", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  End With
  
End Sub

 

[ztodd]

It sounded like your dictionary could just be copied and pasted into a sheet in the Excel file so the words would end up in col A and the definitions in col B. Does that happen?

If so, it wouldn't be too difficult to add to L Howard's code to find each word in your dictionary sheet and insert that definition.

I would modify his code to use the split function and loop through that array, instead of just using replace.

 

[ttdk1]

Suppose the dictionary listing is in Sheet2, where column A list the items (must be single word and lowercase), and column B reveals the meaning of the item.

In Sheet1, column A list the entries, column shows the entries reformatted with parenthesis and associated meaning.

The vba code is a user defined function called processStr. In B1, enter =processStr($A1) into the formula bar and enter. copy down as far as you needed.

Public Function processStr(inputStr As String) As String
Dim dict As Dictionary
Dim Wrd, WrdArr As Variant
Dim newStr As String


  Set dict = New Dictionary


  With Worksheets("Sheet2")
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
      dict.Add .Range("A" & CStr(i)).Value, i
    Next i
  End With
  
  newStr = ""
  WrdArr = Split(inputStr, " ")
  For Each Wrd In WrdArr
    If newStr <> "" Then newStr = newStr & " "
    If dict.Exists(LCase(Wrd)) Then
      newStr = newStr & "(" & Wrd & ") " & Worksheets("Sheet2").Cells(dict.Item(LCase(Wrd)), 2).Value
    Else
      newStr = newStr & Wrd
    End If
  Next Wrd
   
  processStr = newStr
End Function

IF you get "User-defined type not defined" error, you need to go into the VBA editor, click on tools, preferences, then checkbox Microsoft Scripting Runtime to add that in, then click OK to confirm.