Inserting symbols before and after some other text in Excel (macros?)
July 25, 2014 4:09 AM Subscribe
Is it possible to do this in Excel or Word or some other program?: I want to paste a sentence into the a text field, highlight a word or several words in that sentence, hit a keyboard shortcut, and have certain symbols inserted before and after that word or phrase. For example, in the sentence "Metafilter is the best website in the whole wide world," I might highlight "website," press the shortcut, and have it transform into the following: "Metafilter is the best {{c1::website}} in the whole wide world." I'm using OSX and Microsoft Office 2011, but I'm comfortable doing this in a webapp or Google Drive or whatever you suggest! I also have the aText text expansion software, but I can't figure out how to make it insert text at both ends of a highlighted phrase.
As some of you may have guessed from the recognizable syntax, I'm using the Anki spaced repetition software to make cloze deletion flashcards (i.e. "fill in the blank" flashcards). In the native Anki client, there's a helpful keyboard shortcut that does exactly what I'm asking... one card at a time. Problem is, making cards in bulk in Excel and exporting them as .csv files, then importing the .csv file into Anki, fits much better into my workflow and lets me produce cards faster. I can paste my class notes, in the form of a bulleted list, into Excel and turn the important lines into flashcards with ease, while being able to see the whole of the lecture in one glance.
This system works so well for making basic, two-sided Question/Answer flashcards, but I really love the elegance of the cloze deletion cards. Someone might suggest making the fill-in-the-blank cards manually by copying the first column's text into the second column, then using underscores to overwrite the word I want to obscure. I've considered that, but it's not ideal for my purposes for a variety of reasons. Right now, I've made an aText shortcut that expands "[" into "{{c1::" and "]" into }}, but it still means more clicking and more keystrokes than the ideal keyboard shortcut would.
I've tinkered around with macros in Excel a teensy tiny bit, but I haven't found a way to accomplish what I'm looking for. I'm comfortable playing around with and editing code, though my skills are too minimal in any particular language to develop the script I need without either a framework or a tutorial or something. But there must be a way! And Metafilter will surely help me discover it, since it's the best {{c1::website}} in the whole wide world.
As some of you may have guessed from the recognizable syntax, I'm using the Anki spaced repetition software to make cloze deletion flashcards (i.e. "fill in the blank" flashcards). In the native Anki client, there's a helpful keyboard shortcut that does exactly what I'm asking... one card at a time. Problem is, making cards in bulk in Excel and exporting them as .csv files, then importing the .csv file into Anki, fits much better into my workflow and lets me produce cards faster. I can paste my class notes, in the form of a bulleted list, into Excel and turn the important lines into flashcards with ease, while being able to see the whole of the lecture in one glance.
This system works so well for making basic, two-sided Question/Answer flashcards, but I really love the elegance of the cloze deletion cards. Someone might suggest making the fill-in-the-blank cards manually by copying the first column's text into the second column, then using underscores to overwrite the word I want to obscure. I've considered that, but it's not ideal for my purposes for a variety of reasons. Right now, I've made an aText shortcut that expands "[" into "{{c1::" and "]" into }}, but it still means more clicking and more keystrokes than the ideal keyboard shortcut would.
I've tinkered around with macros in Excel a teensy tiny bit, but I haven't found a way to accomplish what I'm looking for. I'm comfortable playing around with and editing code, though my skills are too minimal in any particular language to develop the script I need without either a framework or a tutorial or something. But there must be a way! And Metafilter will surely help me discover it, since it's the best {{c1::website}} in the whole wide world.
Oh, in case it wasn't obvious: you can open the .csv file directly in the text editor and manipulate it between exporting from Excel and importing to Anki.
posted by Phssthpok at 5:54 AM on July 25, 2014
posted by Phssthpok at 5:54 AM on July 25, 2014
Response by poster: Not to threadsit, but the idea is to minimize the number of keystrokes required to do this. Since I'll have to execute that search, I'm looking at five steps, three of which involve chords (multiple keys pressed simultaneously): 1) Highlight phrase, 2) Ctrl+C to copy, 3) Ctrl+F to bring up the search window, 4) Ctrl+V to paste, 5) Enter to execute.
For comparison, my current work-around is fewer steps with no chords: 1) Click at the beginning of the phrase, 2) Press [ (which automatically changes to {{c1:: thanks to aText), 3) Click at the end of the phrase, 4) Press ] (which automatically changes to }}).
The other issue is that I will not want to change every instance of a particular phrase to include the brackets etc., or even necessarily the first instance. So if I have a long document where "website" appears multiple times, but I only want the 5th instance to be changed, I'd have to execute the search multiple times and only apply the "replace" command once it encounters the desired instance. That's not as efficient as I'd like.
Thanks for the suggestion, though - Being reminded that reg-ex exists gives me a new avenue of Googling to do. I'll play around with the text editors you mentioned and see if they have an option that lets me highlight text and then execute a particular reg-ex replacement on the selected portion with a keyboard shortcut.
posted by lizzicide at 6:04 AM on July 25, 2014
For comparison, my current work-around is fewer steps with no chords: 1) Click at the beginning of the phrase, 2) Press [ (which automatically changes to {{c1:: thanks to aText), 3) Click at the end of the phrase, 4) Press ] (which automatically changes to }}).
The other issue is that I will not want to change every instance of a particular phrase to include the brackets etc., or even necessarily the first instance. So if I have a long document where "website" appears multiple times, but I only want the 5th instance to be changed, I'd have to execute the search multiple times and only apply the "replace" command once it encounters the desired instance. That's not as efficient as I'd like.
Thanks for the suggestion, though - Being reminded that reg-ex exists gives me a new avenue of Googling to do. I'll play around with the text editors you mentioned and see if they have an option that lets me highlight text and then execute a particular reg-ex replacement on the selected portion with a keyboard shortcut.
posted by lizzicide at 6:04 AM on July 25, 2014
Response by poster: (Er, sorry, rather than Ctrl I obviously mean Command. Too many years of Windows-based tech support.)
posted by lizzicide at 6:19 AM on July 25, 2014
posted by lizzicide at 6:19 AM on July 25, 2014
Best answer: Broadly speaking, it sounds like you want some kind of macro program. Keyboard Maestro comes to mind.
Alternatively, I can imagine using Text Expander (which is not a macro program, it's a keystroke expander) to do this: set up a shortcut that pops up a dialog into which you'd type the untagged text, and have it type the prefix, text, and suffix. Trigger it on "{{{" or whatever is easy to type but hard to confuse.
But if you're maintaining your data in Excel, you don't need an Excel macro or any of this other stuff to to accomplish what you want. If you've got your untagged text in A1 and you want your tagged text in B1, put this formula in B1:
posted by adamrice at 8:14 AM on July 25, 2014 [1 favorite]
Alternatively, I can imagine using Text Expander (which is not a macro program, it's a keystroke expander) to do this: set up a shortcut that pops up a dialog into which you'd type the untagged text, and have it type the prefix, text, and suffix. Trigger it on "{{{" or whatever is easy to type but hard to confuse.
But if you're maintaining your data in Excel, you don't need an Excel macro or any of this other stuff to to accomplish what you want. If you've got your untagged text in A1 and you want your tagged text in B1, put this formula in B1:
="{{::c1" & A1 & "}}"
Then just drag the little fill-handle down to auto-fill the rest of column B and you're done. That seems like the easiest thing.posted by adamrice at 8:14 AM on July 25, 2014 [1 favorite]
Huh, I thought I posted an answer to this but I guess it didn't go through.
The short version of what I wrote:
a) consider Sticky Keys if chording is a problem
b) I have done exactly what you describe using Word (though a long time ago and not on a mac.) If you want to use Excel's macro system, one way is to record a macro doing what you want. (You'll probably have to play around with it a bit -- recording a find/replace might work differently than recording a copy paste, etc.) Then assign the macro a keyboard shortcut or button from within Excel.
c) That said, a good text editor with regex capabilities should be able to handle this.
posted by trig at 9:01 AM on July 25, 2014
The short version of what I wrote:
a) consider Sticky Keys if chording is a problem
b) I have done exactly what you describe using Word (though a long time ago and not on a mac.) If you want to use Excel's macro system, one way is to record a macro doing what you want. (You'll probably have to play around with it a bit -- recording a find/replace might work differently than recording a copy paste, etc.) Then assign the macro a keyboard shortcut or button from within Excel.
c) That said, a good text editor with regex capabilities should be able to handle this.
posted by trig at 9:01 AM on July 25, 2014
Response by poster: Success! Thanks, adamrice - Your suggestion of Keyboard Maestro not only took care of this issue in a jiffy, it also looks like it'll be super useful for controlling my computer and increasing my efficiency. {{c1::So happy!}}
posted by lizzicide at 10:04 AM on July 25, 2014
posted by lizzicide at 10:04 AM on July 25, 2014
« Older ...and then he did the raccoon-in-my-pants dance. | Pairing fiction with eras in common Newer »
This thread is closed to new comments.
You could run a reg-ex search for:
(website)
and replace it with:
\{\{c1::$1\}\}
And it would fill in $1 with the contents of the first parentheses. And it's relatively easy to extend that to multiple words (together or separately), word patterns, case-insensitive letters, and so on.
posted by Phssthpok at 5:43 AM on July 25, 2014