We have a dataset containing several fruits separated by a comma. There are 3 fruits in each cell. We will split the fruits of column B into 3 different columns (columns C, D, E). Steps: Select the entire dataset and go to Data and choose Text to Columns. The Text to Columns Wizard appears.
Fortunately this is easy to do with built-in Excel functions and the following step-by-step example shows how to do so. First, let’s enter the following dataset that contains comma-separated names of basketball players on various teams:
To split comma separated values in selected cells into columns, the commonly used method is the Text to Column wizard in Excel. Here, I will show you step-by-step how to use this wizard to achieve the desired result.
This article shows you eight ways to split the text into the component parts required by using a delimiter character to indicate the split points. The above sample data will be used in all the following examples. Download the example file to get the sample data plus the various solutions for extracting data based on delimiters.
Navigate to Data > Text to Columns: This opens the wizard where you choose how to split the data. 3. Choose a Delimiter: Select how Excel should divide the data (e.g., by comma or fixed width). 4. Customize the Split: You can add multiple delimiters or even a custom delimiter if your data needs it. 5.
One way to make Excel process all comma-separatedvalues in column B at the same time is to use the REDUCE function in a formula like this: When we enter this formula in cell D5, all results are returned in one go: This is a pretty advanced formula, so let's work through it step-by-step.
Try it! Select the cell or column that contains the text you want to split. Select Data > Text to Columns. In the Convert Text to Columns Wizard, select Delimited > Next. Select the Delimiters for your data. For example, Comma and Space. You can see a preview of your data in the Data preview window. Select Next.
Split text into columns or rows using the Excel TEXTSPLIT() function. Learn the syntax, see examples, and handle complex delimiters without any difficulty.
