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). The following 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:
After exporting the custom search report and opening the Excel spreadsheet, complete the following steps:
- Note that the data in cell E2 is “stacked vertically” and does not contain any delimiters.
- Insert a blank column next to column E.
Note In the example shown, note that a blank column F has been inserted.
- 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.
- Type =SUBSTITUTE either in the cell or in the formula bar.
- 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.
- Click Enter.
- Note that data from cell E2 has now been reformatted within cell F2.
- Double-click the lower-right corner of the cell in order to apply the formula to the remaining cells in the column.
- 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.
Monday, August 19, 2019