MS Excel: Formatting Tasks

1 minute read

Description:

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:

To Resolve:

  1. Using Notepad++, we first want to remove the white space:
    • Copy and paste text from whatever source into Notepad++
    • 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.
  2. Next, capitalize every word: 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:

    1
    2
    
    find: w+  
    Replace: u$0
    
  3. 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.

    • Highlight column A. Click Conditional Formatting => Create New Rule => Use this formula to determine which cells to format => Enter the formula: =countif($B:$B, $A1) => Click the Format button and change the Font color to something you like.
    • Repeat the same for column B, except use this formula and try another font color =countif($A:$A, $B1) Using a Separate Column
    • 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")
    • 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.
  4. To get columns to match text lists:

    • I copy and paste from Notepad++ to Excel.
    • Go to Data tab => Text to columns => Delimeter: Space for each word. This gives me many columns with one word each.
    • Lastly, you just combine column data by following this MS article. Essentially, next open cell type =(A1&" "&B1&" "&C1)
    • 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: &","&

References:

“How to compare two columns and find differences in Excel?”

-----------------------------------------------------------
Spotted a mistake in this article? Why not suggest an edit!
Did you like the article? Donations are always welcome!
-----------------------------------------------------------

Comments