How to split cell by "text" in csv?

How to split cell by "text" 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 some "text" 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 by "text", you can use the Text to Columns feature. Here are the steps:

• Select the cell or range of cells you want to split
• Go to the "Data" tab on the ribbon, and click "Text to Columns"
• In the "Text to Columns" wizard, select "Delimited" and click "Next"
• Under "Delimiters", check the box next to "text" and uncheck any other boxes
• Click "Finish" and the cell contents will be split into separate columns based on the "text".

Note: If your data contains other characters besides the "text", such as spaces, you may need to repeat the process using different delimiters to achieve the desired result.

Method B：Python

You can split cells in an Excel spreadsheet using Python. To do this, you can use a library such as Pandas, which provides functions for reading and manipulating data in a spreadsheet-like format.

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

import pandas as pd

# Load the Excel file into a pandas dataframe
df = pd.read_excel('file.xlsx')

# Split the contents of a cell based on a delimiter
df['column_name'] = df['column_name'].str.split("text", expand=True)

# Write the resulting dataframe back to an Excel file
df.to_excel('file_split.xlsx', index=False)

In this example, the pd.read_excel function is used to load the contents of an Excel file into a pandas dataframe. The df['column_name'].str.split method is then used to split the contents of the specified cell ( df['column_name'] ) based on the text delimiter ("text"). Finally, the resulting dataframe is written back to a new Excel file using the df.to_excel method.

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("text");
                    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 "text".
• You can then download the files and save it to your computer.