MS Excel: Formatting Tasks

1 minute read


In my current position, I find myself needing to take list and convert them into tables in Excel. These are some things I have done:

Step 1 – Notepad++:

Removing white space:

  1. Copy and paste text from whatever source into Notepad++

  2. Use ALT+SHIFT and the arrow keys to select empty regions and then the up or down arrow to remove empty characters at the beginning of lines.

Capitalizing Every Word:

  1. Launch Find and Replace, change the Search Mode radio button to “Regular Expression” and then fill in the following in the what/with text boxes:

find: w+
Replace: u$0

Step 2 – Into Excel:

Once copied and pasted from Notepad++ to Excel, I sometimes have to cross compare two columns of data. The easiest way I have found to do this is using conditional formatting.

  1. Highlight column A. Click Conditional Formatting > Create New Rule > Use this formula to determine which cells to format > Enter the ff. formula:

=countif($B:$B, $A1)
Click the Format button and change the Font color to something you like.

  1. Repeat the same for column B, except use this formula and try another font color.

=countif($A:$A, $B1)
Using a Separate Column

  1. In column C, enter the ff. formula into the first cell and then copy it down.

=if(countif($B:$B, $A1)<>0, “-“, “Not in B”)

  1. In column D, enter the ff. formula into the first cell and then copy it down.

=if(countif($A:$A, $B1)<>0, “-“, “Not in A”)

Both of these should help you visualize which items are missing from the other column.

To get columns to match text lists:

  1. I copy and paste from Notepad++ to Excel.

  2. Go to Data tab – Text to columns – Delimeter: Space for each word. This gives me many columns with one word each.

  3. Lastly, you just combine column data by following this MS article.

a. Essentially, next open cell type:

=(A1&” “&B1&” “&C1)

b. Replace A1, B1, C1 with whatever cells you want to combine. Also note that you are adding a white space in this case, feel free to remove it by just using “&” by itself or using a comma, like this: “&”,”&” without the outside quotes of course.