Course Content

Course Content

Split Data in MS Excel

How to split data into multiple columns

Now we will see that we can split our existing data into multiple columns according to our requirement in MS Excel.

For example we have and column of employee name in which it is with first name and last name both together. But now we want to separate first name and last name into two different columns then we will first insert two column next to employee name column, then we will select that employee name column and then from our DATA tab we will select text to column option.

Inside that we will need to choose the option that how we want to separate our field, separate it with the help of commas or space, or some other fixed process. Then next we will select our separator that is space, and provide with the destination.

This well separate the first name and last name of our employees in two different columns.


  • Select multiple cells to split by clicking in one of them.
  • Split Cells may be found under Table Tools, on the Layout tab, under the Merge group.
  • Enter the number of columns or rows into which you want the chosen cells to be split.

To do so, simply click on the header ( A , B , C , etc.). Then pick "Insert 1 right" from the small triangle. To make a second free column, repeat the process. Write =SPLIT(B1,"-") in the first free column, where B1 is the cell you want to split and - is the character you want the cell to split on.

  • To convert your data to a table, select one cell and click Ctrl+T.
  • Choose From Table in the Power Query tools.
  • Choose the column that contains your products.
  • Select Advanced Options from the Split Column dialogue.
  • Select Rows from the Split Into section.

'=LEFT(B1, FIND(” “,B1)-1) The first word in the text in cell B1 is extracted using this formula. The LEFT function extracts the left-most characters, while the FIND function's output specifies the amount of characters you need to remove.'

Recommended Courses

Share With Friend

Have a friend to whom you would want to share this course?

Download LearnVern App

App Preview Image
App QR Code Image
Code Scan or Download the app
Google Play Store
Apple App Store
598K+ Downloads
App Download Section Circle 1
4.57 Avg. Ratings
App Download Section Circle 2
15K+ Reviews
App Download Section Circle 3
  • Learn anywhere on the go
  • Get regular updates about your enrolled or new courses
  • Share content with your friends
  • Evaluate your progress through practice tests
  • No internet connection needed
  • Enroll for the webinar and join at the time of the webinar from anywhere