Three ways to split your text into columns

Three ways to split your text into columns




When you're working with large datasets, you often have the problem of messy, inconsistent formatting, which makes analysis difficult. This is especially true when the data is being collected elsewhere and then imported to the spreadsheet, which can result in multiple data points piling up in a single cell. Here are three ways to clean and organize your data for easier and meaningful analysis.

Text to Column 

Let's say you have employee names in a single column and you want to split them into first and last names. You can skip manually re-entering data by using Text to Columns.
Here's how you can do it:

Step 1: Select the cells that contain your employee names.
Step 2: Go to Data > Text to Columns.
Step 3: Choose the delimeter (for example, a space or a comma) and select the destination for your output.
Step 4: Preview the output and click OK.


Pattern Fill 

Let's say your data is inconsistent and not separated by the same delimiter, but you want to split them uniformly. Pattern fill saves time by organizing data using predicted values based on a sample group or existing dataset.

Here's how you can do it:

Step 1: Enter the expected output for the first few rows of data.
Step 2: Select a range containing the data and the cells to be filled.
Step 3: Go to Data > Pattern Fill.

Zoho Sheet will detect the pattern and apply it to the selected range.


=SPLIT(text, delimiter) 

If you want to split text like Text to Columns and Pattern Fill but keep it dynamic, then use the =SPLIT() function.