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

Singh_Edm

New Member
Joined
Dec 18, 2013
Messages
30
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
(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

Code:
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
 
Last edited:

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
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.
 
Last edited:

ttdk1

Board Regular
Joined
May 21, 2014
Messages
189
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.

Code:
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.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top