eRAM: Reformatting Cells for Custom Search Reports in Excel

Overview

When exporting some Custom Search Reports as an Excel spreadsheet, you may find it necessary to reformat cells that don’t correctly separate data with a delimiter (e.g., a comma, semicolon or colon). This procedure demonstrates how to reformat data in these cells to include a delimiter.

The following formula can be used to reformat cell data with a delimiter: 
=SUBSTITUTE(SUBSTITUTE(E2,CHAR(13),””),CHAR(10),”,”)

Step-by-Step Process

After exporting the custom search report and opening the Excel spreadsheet, complete the following steps:

1. Note that the data in cell E2 is “stacked vertically” and does not contain any delimiters.
2. Insert a blank column next to column E.
   Note In the example shown, note that a blank column F has been inserted.
   
3. Click the blank cell next to the first cell that needs to be reformatted (cell F2 in the example shown) in order to highlight it.
4. Type =SUBSTITUTE either in the cell or in the formula bar.
   
5. Copy the following text and then paste it to the end of the formula you started in the previous step: (SUBSTITUTE(E2,CHAR(13),""),CHAR(10),",")
   Note The example shown is reformatting cell E2 and using a comma delimiter, however, you will need to edit these parts of the formula as needed.
   
6. Click Enter.
7. Note that data from cell E2 has now been reformatted within cell F2.
8. Double-click the lower-right corner of the cell in order to apply the formula to the remaining cells in the column.
   
9. Once all the necessary data has been reformatted correctly in the new column(s) you’ve created, you can delete the original columns and save the Excel file.