# Custom Vim Functions to Format Your Text

Vim is an amazing text editor and over the years has allowed me to be far more efficient when writing code or editing text in general. Although the initial learning curve is a bit steep, it’s well worth the time to learn to navigate and edit files without your mouse. But what makes Vim even more powerful is that it’s hackable - if you find yourself executing a sequence of keystrokes over and over for certain tasks, you can create your own function that can be used throughout Vim.

Sometimes I get Excel spreadsheets from clients who want me to look at data related to a list of numbers they provided me in an Excel column. If I were to paste those numbers in Vim, I’d get something that looks like the following:

300418944
300404780
300482301
300354016
300295311
300417275
300409184
300480616
300478444
300491475
300478160
300482299
300482959
300154869


If I were to use that list of numbers as a SQL list in the WHERE clause of a SQL query, I’d need to surround all numbers by quotes and put a comma at the end of each number. Finally, I’d need to collapse all the rows into one line and surround that line with parentheses. Essentially, I need to take those numbers and create a tuple. So I want something that looks like this:

('300418944', '300404780', '300482301', '300354016', '300295311', '300417275',
'300409184', '300480616', '300478444', '300491475', '300478160', '300482299',
'300482959', '300154869')


Doing that by hand would take quite a bit of time, especially if given a list of hundreds of numbers. This is where Vim shines - we can create a function in our vimrc file that will handle these steps for us.

" convert rows of numbers or text (as if pasted from excel column) to a tuple
function! ToTupleFunction() range
silent execute a:firstline . "," . a:lastline . "s/^/'/"
silent execute a:firstline . "," . a:lastline . "s/$/',/" silent execute a:firstline . "," . a:lastline . "join" silent execute "normal I(" silent execute "normal$xa)"
silent execute "normal ggVGYY"
endfunction
command! -range ToTuple <line1>,<line2> call ToTupleFunction()


This function will not only format your text, but also copy the result to your clipboard so you can paste it in whatever SQL query editor you use.

Let’s break down each line of the body of the function.

silent execute a:firstline . "," . a:lastline . "s/^/'/"


For all visually selected lines, the line above jumps to the beginning of each line and inserts a single quotation mark.

silent execute a:firstline . "," . a:lastline . "s/$/',/"  This line goes to the end of each line and inserts a single quotation mark and comma. The next line of code joins all the lines of text we have so far into one line: silent execute a:firstline . "," . a:lastline . "join"  Now we add an open parenthesis at the beginning of the line: silent execute "normal I("  And then insert the closing one: silent execute "normal$xa)"


The last line of the function selects the entire text and copies it to the clipboard (I have a custom mapping for copying to the clipboard: vnoremap YY "*y).

At last, here’s the function in action:

If you’d like to have a similar function that creates an array instead, you need only make a small change to the ToTupleFunction and give the function a new name.

" convert rows of numbers or text (as if pasted from excel column) to an array
function! ToArrayFunction() range
silent execute a:firstline . "," . a:lastline . "s/^/'/"
silent execute a:firstline . "," . a:lastline . "s/$/',/" silent execute a:firstline . "," . a:lastline . "join" " these two lines below are different by only one character! silent execute "normal I[" silent execute "normal$xa]"
endfunction
command! -range ToArray <line1>,<line2> call ToArrayFunction()