How to split cell by "carriage return" in csv?

How to split cell by "carriage return" in csv?

I often receive these kinds of CSV or Excel files in my work. In a certain cell (column), others will input the content with "carriage return" as the separator. I need to divide this cell (column) into one or more cells(column). I need to split these cells, and then do data analysis. So the question is: how can I do it?

Method A：Excel or Google Spreadsheet

To split a cell in Excel based on a carriage return, you can use the following formula in a separate cell:

=IFERROR(TRIM(MID(SUBSTITUTE($A1,"\n",REPT(" ",LEN($A1))),(ROW(1:1)-1)*LEN($A1)+1,LEN($A1))), "")

This formula uses the SUBSTITUTE function to replace all occurrences of the carriage return character ( \n ) with a series of spaces equal in length to the original cell contents. The TRIM function is then used to remove the extra spaces, and the MID function is used to extract the contents of the cell that correspond to each line of the original cell contents. Finally, the IFERROR function is used to return an empty string if an error occurs.

To split the cell into multiple cells, simply copy this formula to a range of cells immediately below the original cell. Each copied cell will contain the contents of one line from the original cell. To split the cell into a specific number of cells, adjust the range of cells to which the formula is copied.

Method B：Python

You can use Python to split cells in an Excel spreadsheet based on a carriage return. One way to do this is to use the pandas library, which provides a high-level interface for working with data in Python. Here's an example:

import pandas as pd

# Load the Excel spreadsheet into a pandas DataFrame
df = pd.read_excel("file.xlsx", sheet_name="Sheet1")

# Split the contents of the "A1" cell into separate lines
lines = df.at["A1", "A1"].split("\n")

# Write each line to a separate cell in column B
for i, line in enumerate(lines):
    df.at[i, "B"] = line

# Save the updated DataFrame back to the Excel spreadsheet
df.to_excel("file.xlsx", index=False)

In this example, the contents of an Excel spreadsheet are loaded into a pandas DataFrame using the read_excel function. The contents of the "A1" cell are then split into separate lines using the split method. The separate lines are then written to separate cells in column B using the at method. Finally, the updated DataFrame is saved back to the Excel spreadsheet using the to_excel function.

Method C：Java

You can split cells in an Excel spreadsheet using Java. To do this, you can use a library such as Apache POI, which provides Java APIs for reading and writing Microsoft Office files.

Here is an example of how you could split cells in an Excel file using Apache POI:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelSplitter {
    public static void main(String[] args) throws Exception {
        // Load the Excel file
        FileInputStream inputStream = new FileInputStream("file.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);

        // Split the contents of a cell based on a delimiter
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String[] values = cell.getStringCellValue().split("\n");
                    for (int i = 0; i < values.length; i++) {
                        cell = row.createCell(cell.getColumnIndex() + i);
                        cell.setCellValue(values[i]);
                    }
                }
            }
        }

        // Write the resulting Excel file
        FileOutputStream outputStream = new FileOutputStream("file_split.xlsx");
        workbook.write(outputStream);
        workbook.close();
    }
}

The best way：Use QuickTran function

• Visit our website and find the online tool.
• Open your csv file by clicking the "Choose File" button and selecting the file you want to change.
• Once the file has been uploaded, you can select the specified columns you want to be processed , and in seconds, you'll split cell(columns) by carriage return.
• You can then download the files and save it to your computer.