Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page macro to add brackets to each line and add single quotes to each word in the line macro to add brackets to each line and add single quotes to each word in the line
Register FAQ

Reply
Page 1 of 2 1 2
 
Thread Tools Display Modes
  #1  
Old 02-15-2015, 05:23 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default macro to add brackets to each line and add single quotes to each word in the line
Hello


Im looking for a macro that add brackets to the beginning and end of each line and adds single quotes to each word in the line and add a comma at the end of the line.

the reason is I am writing code in Sql and need to prepare some of it first in word.

Line example
AFG FKFKF DDJ DKKFJG DKDKJDJF

I want the line above to look like this after the macro is run
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),

I would like the macro to work on all the lines in the word dcoument.

thanks for any help offered.
Reply With Quote
  #2  
Old 02-15-2015, 06:45 AM
gmayor's Avatar
gmayor gmayor is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default
How about

Code:
Sub Macro1()
Dim oPara As Paragraph
Dim oRng As Range
Dim vWord As Variant
Dim strNew As String
Dim i As Long
    For Each oPara In ActiveDocument.Paragraphs
        Set oRng = oPara.Range
        oRng.End = oRng.End - 1
        oRng.Text = Trim(oRng.Text)
        vWord = Split(oRng.Text, Chr(32))
        strNew = ""
        For i = 0 To UBound(vWord)
            strNew = strNew & Chr(39) & vWord(i) & Chr(39)
            If i < UBound(vWord) Then
                strNew = strNew & Chr(44) & Chr(32)
            End If
        Next i
        strNew = "(" & strNew & "),"
        oRng.Text = strNew
    Next oPara
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 02-15-2015, 06:59 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Thanks gmayor, it works great except there is no comma sperating the text within the brackets such as highlighted in red below. Im sorry as I should have stated that in the original post. Everything else works.

('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),
Reply With Quote
  #4  
Old 02-15-2015, 07:44 AM
gmayor's Avatar
gmayor gmayor is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default
The comma was present in your example.

Change the section below to lose the comma (Chr(44))

Code:
            If i < UBound(vWord) Then
                strNew = strNew & Chr(32)
            End If
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 02-15-2015, 07:55 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Sorry for not being clear.

What I meant is that after I ran your code there is no comma seperating the text in the results.

After the vba is run i would like there to be commas between the values like in the example where they are highlighted in red.
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),


thanks for all your help and taking the time to write the code. I do appreciate it.

I have 29,300 records that need to have brackets and quotes and commas entered so the code is saving me an enormous amount of time.
Reply With Quote
  #6  
Old 02-15-2015, 04:49 PM
Guessed's Avatar
Guessed Guessed is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 32bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 4,160
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default
Does this work?
Code:
Dim oPara As Paragraph
Dim oRng As Range, oParaRng As Range

Set oRng = ActiveDocument.Range
oRng.Text = Replace(oRng.Text, " ", Chr(39) & ", " & Chr(39))
For Each oPara In oRng.Paragraphs
  Set oParaRng = oPara.Range
  oParaRng.MoveEnd Unit:=wdCharacter, Count:=-1
  oParaRng.Text = "('" & oParaRng.Text & "'),"
Next oPara
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #7  
Old 02-15-2015, 10:16 PM
macropod's Avatar
macropod macropod is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,370
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Try:
Code:
Sub Demo()
Application.ScreenUpdating = False
Dim bOpt1 As Boolean, bOpt2 As Boolean
With Options
  bOpt1 = .AutoFormatAsYouTypeReplaceQuotes
  bOpt2 = .AutoFormatReplaceQuotes
  .AutoFormatAsYouTypeReplaceQuotes = False
  .AutoFormatReplaceQuotes = False
End With
With ActiveDocument
  With .Range.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Wrap = wdFindContinue
    .Text = " "
    .Replacement.Text = "', '"
    .Execute Replace:=wdReplaceAll
    .Text = "^13"
    .Replacement.Text = "'),^p('"
    .Execute Replace:=wdReplaceAll
  End With
  .Range.Paragraphs.Last.Range.Cut
  .Range(0, 0).Paste
  .Range.Paragraphs.First.Range.Characters.Last.Delete
End With
With Options
  .AutoFormatAsYouTypeReplaceQuotes = bOpt1
  .AutoFormatReplaceQuotes = bOpt2
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 02-16-2015, 04:44 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Thanks Guessed and macropod for doing up the codes.

Unfortunately they dont work completely. Commas do not show in the results and quotes arent applied to both texts within the brackets.

Both your result are identical after both the codes are run. As you can see there is no comma seperating the text within the brackets and quotes only show at beginning and end of text.

The result from both your codes:

('KAUS AAL'),
('KJFK AAL'),
('KORD AAL'),
('KSAN AAL'),
('CYYZ ACA'),


I would like it to be like this

('KAUS', 'AAL'),
('KJFK', 'AAL'),
('KORD', 'AAL'),
('KSAN', 'AAL'),
('CYYZ', 'ACA'),
Reply With Quote
  #9  
Old 02-16-2015, 05:13 AM
gmayor's Avatar
gmayor gmayor is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default
Now I'm completely confused, because that is what the original macro did:

AFG FKFKF DDJ DKKFJG DKDKJDJF
AFG FKFKF DDJ DKKFJG DKDKJDJF
AFG FKFKF DDJ DKKFJG DKDKJDJF
AFG FKFKF DDJ DKKFJG DKDKJDJF

becomes

('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),
('AFG', 'FKFKF', 'DDJ', 'DKKFJG', 'DKDKJDJF'),


The other contributors macros removed the commas as per your follow up instructions, whereas my second macro doesn't insert the commas in the first place.

In what way does the first macro not work for you?
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 02-16-2015, 05:41 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Gmayor your code does not add commas between the text in the brackets. Nor does it add quotes to each text in the brackets. This is the result when i use your code


('KAUS AAL'),
('KJFK AAL'),
('KORD AAL'),
('KSAN AAL'),
('CYYZ ACA'),


I would like it to be like this

('KAUS', 'AAL'),
('KJFK', 'AAL'),
('KORD', 'AAL'),
('KSAN', 'AAL'),
('CYYZ', 'ACA'),
Reply With Quote
  #11  
Old 02-16-2015, 06:05 AM
Guessed's Avatar
Guessed Guessed is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 32bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 4,160
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default
The reason none of these macros is doing what they should is because you have something other than a space between the words. Each attempt has been looking for a space to replace and the fact that it is not working tells us that the separator is something other than what you pasted into the original request.

You are going to need to post your actual document if you want code that works on it.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #12  
Old 02-16-2015, 06:29 AM
gmayor's Avatar
gmayor gmayor is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default
In my original code the paragraph is split with

Code:
vWord = Split(oRng.Text, Chr(32))
i.e. it splits at the space character 'Chr(32)' between the 'words'. However it can only split at spaces if there are spaces, and if you don't get the commas and single quotes, the 'spaces' are not space character 32, but probably non-breaking spaces 'Chr(160)'.

Change the 32 to 160 and see if that fixes it. If not, then, as Andrew indicates, we need to see a document sample.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #13  
Old 02-16-2015, 06:30 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Thanks for pointing that out.

I have the original data in an excel table. This data needs to be Inserted in the Sql database editor. I need to add the quotes,commas,brackets to it before i insert it.

I therefore copied and pasted this data from excel to word as text only in order to add brackets and quotes etc.

I turned on the show hide button to check if the spacing is correct and it shows a tab marker(arrow) seperating the words instead of a space marker.

I know its asking a lot but is it posisble you could alter the code.
Reply With Quote
  #14  
Old 02-16-2015, 07:17 AM
bracketandquotes bracketandquotes is offline macro to add brackets to each line and add single quotes to each word in the line Windows 8 macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Novice
macro to add brackets to each line and add single quotes to each word in the line 		 
Join Date: Feb 2015
Posts: 8
bracketandquotes is on a distinguished road
Default
Gmayor, 160 didnt work. Ive attached a sample doc as original is too big. Hope it uploads correct
Attached Files
File Type: docx For Web.docx (12.5 KB, 16 views)
Reply With Quote
  #15  
Old 02-16-2015, 07:25 AM
gmayor's Avatar
gmayor gmayor is offline macro to add brackets to each line and add single quotes to each word in the line Windows 7 64bit macro to add brackets to each line and add single quotes to each word in the line Office 2010 32bit
Expert
  
Join Date: Aug 2014
Posts: 4,137
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default
Use Chr(9) in place of Chr(32) for the tab.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to add brackets to each line and add single quotes to each word in the line How many points is line spacing 'single'? Ugeen Word 2 11-24-2013 03:14 PM
macro to add brackets to each line and add single quotes to each word in the line Changing single-quotes to double-quotes Bobosmite Word 5 04-15-2013 06:40 AM
From the command line, how do I run a Word macro? jdockstader Word 7 02-04-2013 06:09 AM
Single Line Spacing for Short Lists? tatihulot Word 2 04-20-2010 02:53 PM
macro to add brackets to each line and add single quotes to each word in the line Different alignments in a single line George99 Word 2 01-24-2010 05:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:14 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft