Splitting an Excel column by delimiter

You’re bound to run into an excel sheet where differing data types are joined together in a single column. For example in the following data dates are combined with event names making it impossible to filter or sort by either date or event. By using Excel’s “Text to Columns” feature, we can separate these two distinct data points.

As long as you have a consistent delimiter (in my case, the pipe character “|”) or a pre-determined fixed width, you can easily move the text following the delimiting character (or exceeding the specified width) to the adjacent column (which I’ve inserted in advance to prevent overwriting existing data in the original second column).

  1. Select column or cells you’re splitting
  2. Select the “Data tab” then “Text to Columns”
  3. Choose to split by “delimiter.” Click next.
  4. If your delimiter isn’t an option, enter it as I’ve done (max 1 character) in the “Other” space. Click next.
  5. You can choose to change data formats here, so I’ve made my new first column date format for example. You can always change column data types later as well.
  6. Finish

Now you can sort and filter correctly on the values you separated.

Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:

“DataFormat.Error: Invalid cell value ‘#DIV/0!’.” 

You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.

It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.

=IFERROR(Your_Formula, Value_If_Error)

2018-10-10_09-12-39.gif

Solution: Excel drag to “fill” not working – value is copied, formula ignored

A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:
2018-09-25_11-29-39.gif

Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”