How to Split Cells in Excel?

Most often we need to split cells for text processing [Eg. Split Last Name and First Name from Customer name field].

We explore two different methods to split cells in Excel:

Approach 1: Text to Column

Step 1: Select the entire data set [Range A1 – A11]

Step 2: Go to “Data” >> Click “Text to Columns”, to popup Convert Text to Columns Wizard

Step 3: Select “Delimited” and Press “Next”.

Step 4: Check “Space” and Press “Next”

Step 5: Change Destination to $B$1 cell and Press “Finish”

Approach 2: Using Formulas

Step 1: Type Header text “Product” and “Category” to cells B1 and C1 respectively

Step 2: Write the below formula in cell B2 to split the first word (Product)

=LEFT(A2,FIND(" ",A2)-1)
Step 3: Fill the formula in cells B2:B11

Step 4: Write the below formula in cell C2 to split the last word (Category).

=RIGHT(A2,LEN(A2)-FIND(" ",A2))
Step 5: Fill the formula in cells C2:C11.

Permanent