the Power of Regex

Today I solved a problem during my work in help of vim and regex. Take a note here, in case I forget it someday.

There are two files(excel A and word B) for me to figure out whether the names in word B are also existed in excel A. Excel A and word B look like this:

Excel A:

From Drop Box

Word B:

From Drop Box

First of all, if I want to compare the two files, I should copy the names in word B to excel A and use the function vlookup, this suppose to be simple, so I don’t want to talk about this part. The main problem here is how to transform the names in word B to some other format, each name in a row actually. So, the objective is set, let’s begin to solve it.

  1. Copy the entire word B to gvim C, because vim is truly a word editor and it is where the regex can take full advantage of.
  2. There are two space between each name, we should turn them to one. It’s time for regex to roll out.

    :%s/ss/ /gc

    “s” means match a white space character, exactly a space and a tab.

  3. And some names contain two character, but they also have a space between them, we should eliminate these space.

    :%s/s(S)s/ 1/gc

    “S” means match anything that isn’t a space and a tab and “(…)” means making matched subtexts available in the replacement part of a substitute command, such as “1”.

  4. Replace every space to a carriage return, this one seems a piece of cake.


    “r” means a carriage return.

  5. Copy the entire contexts to excel A, and transfer the job to function vlookup. The contexts should look like this:
  6. From Drop Box

If the excel A and word B are some big files, we’ll save great amount of time using regex. In my case, they really are. And I think I did my job nice and easy.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>