Very common need: Copy some data into an Excel cell from an arbitrary other source (including another Excel sheet, or webpage, etc.). In the process, strip any external formatting, HTML tables, etc. with extreme vengeance and only paste the plain text.
Traditional way: Mouse fandango (Excel 2013: Home -> Paste -> Paste Special…->Text->OK) for every time you want to do this.
Better: Create “PastePlainEffingText” macro activated by a nice fast keyboard shortcut equivalent to Ctrl-V. Store this macro persistently in the Excel “Personal Workbook”, not the currently open document, so it is available in any open document.
Steps:
1) View -> Unhide -> Personal etc. (The ‘Personal’ workbook is hidden by default. Attempting to save a macro to it generates an extremely helpful message saying to use the ‘unhide’ option, without giving the option to just do this, nor telling you where this setting is.)
2) View -> Record Macro
3) Mousedance as above (Paste Special etc.)
4) View -> Stop Recording
5) Assign keyboard shortcut. I just assigned it to “Ctrl-B” since it’s right next to Ctrl-V. This means I can no longer Ctrl-B to make text bold, but for the once-a-year I’d actually want to do this, it’s a plenty acceptable tradeoff.
6) Optional: Re-hide the “Personal” workbook.
Caveats:
When assigning the keyboard shortcut, the “Ctrl” portion is mandatory and cannot be changed. Excel will automatically insert a ‘Shift’ in addition to this if you happened to type an uppercase letter in the sole letter box provided (they way keyboard shortcuts are usually represented in text). This is somewhat unintuitive and does not mean Excel is blocking you from overwriting an existing shortcut – just change the letter to lowercase and it’ll go away. There is no warning if you do overwrite an existing shortcut, so you’ll have to check on this yourself.
At the time of this writing, Excel does not allow writing an actual macro (code) in the Personal Workbook directly. You have to ‘Record Macro’ and physically do whatever action/mousedance to initially generate the equivalent code. But once this is done, you can edit the actual code. To write/paste an arbitrary code macro, you can probably just “Record Macro” some trivial dummy operation (paste some text, etc.) then just replace the autogenerated code with your own.
The equivalent code for this macro is:
Sub PastePlainEffingText()
'
' PastePlainEffingText Macro
' Strip formatting when pasting buffer contents
'
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
End Sub
Leave a Reply