WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Split Cells in Excel

Split Cells in Excel

Splitting cells in excel means splitting the data present in excel in two or more than two parts in another column, splitting is done by text to columns in excel where we have two options as delimited which uses a delimiter to split the cells and fixed-width where we define the width from we want to split the cells, text to columns can be accessed from the data tab or keyboard shortcut ALT+ A+ E.

How to Split a Cell in Excel

Sometimes you need to split cells in excel if they contain multiple sets of data that you want to analyze independently. Suppose one column contains addresses, including street addresses and Zip codes.

You can download this Split a Cell Excel Template here – Split a Cell Excel Template

You have seen most of the time while working on Excel that data is organized into columns, and each column contains specific information associated with a certain row. For example, one column might contain the customer’s name, Customer’s DOB, contact number, address, etc.

In this article, we will learn how to split cells in excel using Text to Column Method

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Split Cells in Excel Using Text to Column method

We have given some list of names, including Full name.

Split Cells in Excel Example 1

Follow the below steps to split cells in excel these names into the First Name & Last Name separately.

  • Select the whole-cell range which you want to split. Here we have selected the range from A3:A10. Refer to the below screenshot.

Split Cells in Excel Example 1-1

  • Go to the DATA tab.
  • Click on the Text to Columns option under the Data Tools section. See the below screenshot.

Split Cells in Excel Example 1-2

  • It will open a Convert Text to a Column dialog box like the below screenshot.

Split Cells in Excel Example 1-3

Let us elaborate on the two ways – Delimited Option and Fixed Width.

Method #1 – Split Cells in Excel Using Delimited Option

  • This is the 3 step process.
  • Make sure the Delimited option is selected (It is by default selected here). This option allows you to separate the first name and the last name based on a specified separator. In our example, the space bar is a separator here. Refer to the below screenshot.

using Delimited option step 1

  • Click on the Next button.
  • The 2nd step screen will appear.

using Delimited option step 1-1

  • Select the checkbox for Space as the delimiter under the Delimiters section and deselect other options here (if selected). Refer to the below screenshot.

using Delimited option step 1-2

  • Click on the Next button.
  • The 3rd screen will appear here. In this step, you can specify the destination where you want to see the result. The default destination here in Cell A4 as shown in below screenshot.

using Delimited option step 1-3

  • If you continue with the default destination cell address, it will replace the original data set. If you want to keep the original dataset as it is, selects another cell as the destination.
  • In this example, B4 is selected.
  • Click on the Finish button.

The final result is shown below:

using Delimited option step 1-4

Method #2 – Split Cells in Excel Using Fixed Width Option

Suppose you want to split the text at a particular point. Use this option.

We have given the list of Date & time, and we need to split this list.

Using Fixed Width Step 1

Follow the below steps:

  • Select the cell’s range.
  • Go to the DATA tab.
  • Click on the Text to Columns option under the Data Tools section.

Using Fixed Width Step 1-1

  • It will open a dialog box to convert Text to Columns wizard. This wizard has 3 step process. Refer to the below screenshot. Select the Fixed Width option.

Using Fixed Width Step 1-3

  • Click on the Next button.
  • It will appear on the 2nd step screen like the below screenshot.

Using Fixed Width Step 1-4

  • Under the Data Preview section, we can create a broken line and place it at the desired position on the text where we want to split cells in excel.
  • By double-clicking on the line, we can remove the break line too.
  • Now we will place the break line according to our desired results.
  • As in our example, we want to split cells in excel forgiven the list into two parts: Date & Time.
  • Click on the Next button, and it will appear in the 3rd step process.
  • You can now select how to import each new column. Choose the data type under the Column data format section, as shown in the below screenshot.
  • Select the destination cell address where you want the results.
  • Click on the finish.

Using Fixed Width Step 1-5

The final result is shown below:

Using Fixed Width Step 1-6

Things to Remember about Split Cell in Excel

  • Text to Column feature splits the text of the cells based on the delimiter.
  • The result of the method “Text to Column” is static. If you make any changes to the original data, it won’t impact the results. You need to repeat the whole process again for the desired results.

Recommended Articles

This has been a guide to Split a cell in Excel. Here we discuss how to split a cell in excel using text to column (Delimited and Fixed Width method) along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Convert Excel Text to Numbers
  • Combine Cells in Excel
  • Merge Cells in Excel
  • Unmerge Cells in Excel
  • Change Case in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Split a Cell Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More