by How do you split a cell in Excel? By using the Text to Columns feature, Flash Fill, formulas or Split Text tool. This tutorial outlines all the options to help you choose the technique best suited for your particular task.
Generally, you may need to divide cells in Excel in two cases. Most often, when you import data from some external source where all information is in one column while you want it in separate columns. Or, you may want to separate cells in an existing table for better filtering, sorting or a detailed analysis.
How to split cells in Excel using Text to Columns
The Text to Columns feature comes in really handy when you need to split cell contents into two or more cells. It allows separating text strings by a certain delimiter such as comma, semicolon or space as well as splitting strings of a fixed length. Let's see how each scenario works.
How to separate cells in Excel by delimiter
Suppose, you have a list of participants where a participant name, country and expected arrival date are all in the same column:
What we want is to separate data in one cell into several cells such as First Name, Last Name, Country, Arrival Date and Status. To have it done, perform the following steps:
- If you want to place the results in the middle of your table, start by inserting a new column(s) to avoid overwriting your existing data. In this example, we have inserted 3 new columns like shown in the screenshot below:
- Select the cells you want to divide, navigate to the Data tab > Data Tools group, and click the Text to Columns button.
- In the first step of the Convert Text to Columns wizard, you choose how to split cells - by delimiter or width.In our case, the cell contents are separated with spaces and commas, so we select Delimited, and click Next.
- In the next step, you specify the delimiters and, optionally, text qualifier.You can choose one or more predefined delimiters as well as type your own one in the Other box. In this example, we select Space and Comma:
Tips:
- Treat consecutive delimiters as one. Be sure to select this option when your data may contain two or more delimiters in a row, e.g. when there are a few consecutive spaces between words or the data is separate by a comma and a space, like "Smith, John".
- Specifying the text qualifier. Use this option when some text is enclosed in single or double quotes, and you'd like such portions of text to be inseparable. For example, if you choose a comma (,) as the delimiter and a quotation mark (") as the text qualifier, then any words enclosed in double quotes, e.g. "California, USA", will be put into one cell as California, USA. If you select {none} as the text qualifier, then "California will be distributed into one cell (together with an opening quotation mark) and USA" into another (together with a closing mark).
- Data preview. Before you click the Next button, it stands to reason to scroll through the Data preview section to make sure Excel has split all cells contents right.
- Just two more things are left for you to do - choose the data format and specify where you want to paste the resulting values:
- Data format. By default, the General format is set for all columns, which works well in most cases. In our example, we need the Data format for the arrival dates. To change the data format for a particular column, click on that column under Data preview to select it, and then choose one of the formats under Column data format (please see the screenshot below).
- Destination. To tell Excel where you want to output the separated data, click the Collapse Dialog icon
next to the Destination box and select the top-leftmost cell of the destination range, or type a cell reference directly in the box. Please be very careful with this option, and make sure there are enough empty columns right to the destination cell.
Notes:
- If you do not want to import some column that appears in the data preview, select that column and check Do not import column (skip) radio button under Column data format.
- It is not possible to import the split data to another spreadsheet or workbook. If you attempt to do this, you will get the invalid destination error.
- Finally, click the Finish button and you are done! As shown in the below screenshot, Excel has perfectly placed the contents of one cell into several cells:
How to split text of a fixed width
This section explains how to divide a cell in Excel based on the number of characters you specify. To make things easier to understand, please consider the following example.
Supposing, you have Product IDs and Product names in one column and you want to extract the IDs into a separate column:
Since all of the product IDs contain 9 characters, the Fixed width option fits perfectly for the job:
- Start the Convert Text to Columns wizard as explained in the above example. In the first step of the wizard, choose Fixed width and click Next.
- Set the width of each column by using the Data preview section. As shown in the screenshot below, a vertical line represents a column break, and to create a new break line, you simply click at the desired position (9 characters in our case):
- In the next step, choose the data format and destination for the split cells exactly as we did in the previous example, and click the Finish button to complete the separation.
How to separate cells Excel with Flash Fill
Beginning with Excel 2013, you can make use of the Flash Fill feature that can not only automatically populate cells with data, but also split cell contents.
Let's take a column of data from our first example and see how Excel's Flash Fill can help us split a cell in half:
- Insert a new column next to the column with the original data and type the desired part of the text in the first cell (participant name in this example).
- Type the text in a couple more cells. As soon as Excel senses a pattern, it will populate similar data into other cells automatically. In our case, it's taken 3 cells for Excel to figure out a pattern:
- If you are satisfied with what you see, press the Enter key, and all the names will be copied to a separate column at once.
How to split cell in Excel with formulas
Whatever diverse information your cells may contain, a formula to split a cell in Excel boils down to finding a position of the delimiter (comma, space, etc.) and extracting a substring before, after or in-between the delimiters. Generally, you'd use SEARCH or FIND functions to determine the delimiter's location and one of the Text functions (LEFT, RIGHT or MID) to get a substring.
For example, you'd use the following formulas to split data in cell A2 separated with a comma and space (please see the screenshot below):
To extract the name in B2:
=LEFT(A2, SEARCH(",",A2)-1)
Here, the SEARCH function determines the position of a comma in A2, and you subtract 1 from the result, because the comma itself is not expected in the output. The LEFT function extracts that number of characters from the start of the string.
To extract the country in C2:
=RIGHT(A2, LEN(A2)-SEARCH(",", A2)-1)
Here, the LEN function calculates the total length of the string, from which you subtract the position of the comma returned by SEARCH. Additionally, you subtract the space character (-1). The difference goes to the 2nd argument RIGHT, so it pulls that many characters from the end of the string.
The result will look as follows:
If your delimiter is a comma with or without space, you can use the following formula to extract a substring after it (where 1000 is the maximum number of characters to pull):
=TRIM(MID(A2, SEARCH(",", A2)+1, 1000))
As you see, there is no universal formula that could handle all kinds of strings. In each particular case, you will have to work out your own solution.
The good news is that the dynamic array functions that appeared in Excel 365 make the use of many old formulas unnecessary. Instead, you can use these functions:
- TEXTSPLIT - split strings by any delimiter that you specify.
- TEXTBEFORE - extract text before a specific character or substring.
- TEXTAFTER - extract text after a certain character or word.
For more formula examples to divide cells in Excel, please check out the following resources:
Split cells using Split Text feature
Now that you are familiar with the inbuilt features, let me show you an alternative way to divide cells in Excel. I mean the Split Text tool included with our Ultimate Suite for Excel. It can perform the following operations:
- Split cell by character
- Split cell by string
- Split cell by mask (pattern)
For example, splitting the participant details in one cell into several cells can be done in 2 quick steps:
- Select the cells you want to separate, and click the Split Text icon on the Ablebits Data tab, in the Text group.
- On the add-in's pane, configure the following options:
- Select Comma and Space as the delimiters.
- Select the Treat consecutive delimiters as one check box.
- Choose Split to columns.
- Click the Split button.
Done! Four new columns with the split data are inserted between the original columns, and you only need to give those columns appropriate names:
Tip. To separate a column of names to the first name, last name and middle name, you can use a special Split Names tool.
If you are curious to see the Split Text and Split Names tools in action, we are welcome to use the download link below. I thank you for reading and hope to see you on our blog next week!
Available downloads
Ultimate Suite 14-day fully-functional version (.exe file)
189 comments
Hi Team,
I need to split the data and get texts starts with INC and CRQ separately.
GERP Evolve services - Consumption report June-November 2013 Ann Schroyens € 2,028.22 CRQ700000956750 € 126.74 CRQ700000974595 € 548.04 CRQ700000999790 € 1,005.47 INC700023629023 € 27.88 INC700023661405 € 27.88 INC700023737013 € 139.64 INC700024270751 € 27.91 INC700024290887 € 27.91 INC700024386325 € 27.91 INC700024386855 € 27.91 INC700024506369 € 27.91 INC700024622160 € 13.01 Diego Ferreira € 55.83 INC700024445871 € 27.91 INC700024445922 € 27.91 Evert Vannoppen € 3,075.83 CRQ700000968131 € 3,075.83 Jarod Lee € 40.48 INC700023507897 € 40.48 Pascal Hereng € 186.42 CRQ700000927347 € 186.42 Timmy Torfs € 885.47 CRQ700000855850 € 885.47
Thank YOU !!
How to split 1 cell data to 2 cell data, Ex: A01M01 => A01 / M01
Hi Ratanak,
For this particular example, you can use the RIGHT and LEFT functions:
To extract the first 3 chars: =LEFT(A1, 3)
To extract the last 3 chars: =RIGHT(A1, 3)
Exp.
NO 86 PANCHALAVAS SONETH PO VAV TE SONETH LANDMARK :
NO 195 RAJHAPUTWAS PO SUIGAM SUIGAM LANDMARK :
Dived village & po
how to split or deviation one cell
Hi! What shall I do with my document I want that my first column will see the column j. I find it hard to see the document I need to edit.
Hello, Jedd,
You can scroll until your column J is the first and click Save. After this when you open the workbook next time, this view will be kept. Or you can hide columns A-I.
Hi Svetlana Cheusheva,
You are simply Awesome in Your job! The way you defined things in order excellent! Thanks
Are you saying that Excel cells cannot be split the same way they can be split in Word?
Hello, Mary Jane,
Could you clarify what you mean by splitting cells in Word?
Hi Siva
you can use formula =CONCATENATE(A1,B1)
Like A1 is aaaa and B1 has 111111 so the result should be aaaaa 11111
Thanks
You are great and doing good job, Svetlana Cheusheva
This is my sheet.
aaaaa
111111
bbbbbb
2222222
cccccccc
33333333
ddddddd
44444444
And i need to achieve like this.
aaaaa 111111
bbbbbb 2222222
cccccccc 33333333
ddddddd 44444444
HI,
I want to know the formula how to separate from the below format
"{otherPartySiteInstanceId:[94462982],
duLocatorId:[Dom Ult has been subscribed already with Locator 1001034331.],
otherPartySiteId:[86485522],
businessRelationshipItemId:[3584087],
guLocatorId:[Global Ult has been subscribed already with Locator 1001034331.],
organizationID:[19989256],
businessRelationshipId:[502011447],
customerLocatorID:[1003343591],
addressCountryLanguageId:[126951852],
addressId:[25175801]}"
I need to separate the organization id to next column, format might be different
Any ideas on how I can do this?
thanks in advance
Hi
Pls help me that in excel can it possible that i want fill a column figure and the next two or three column auto fill with special text (for example if i fill in column A value 100 and column B & C auto filled "PAID"
This is great stuff, but I have one that I can't figure out and would love some input. I have a column that includes numbers and dates. I need to move the date rows to a new column:
0.5h
0.5h
0.25h
TUE, MAR 24, 2015
1h
1h
WED, MAR 18, 2015
0.5h
0.5h
TUE, MAR 17, 2015
3h
FRI, MAR 13, 2015
0.75h
THU, MAR 12, 2015
0.5h
4.25h
WED, MAR 11, 2015
Any ideas on how I can do this? I can't event select the date fields and do a copy and paste because it doesn't leave them in the same row...
Hello,
Since it's necessary to check the data formats, you need a special VBA macro for your task. Sorry, we cannot help you with this.
Hai i am harish in want two answer first how can i divided in this number 1(23)345 like 1 23 345 in three column kindly in use right left and mid formulas
secend how can i convert in number to word in excel
thanks for your information, i have one dought one sentence in
one column that is "branch, sno, subject, name, city" in this question i want in A column "sno", B column "branch", and C-"subject", D-"city", E-"branch". Please suggest how it is solved.
I am utilizing google doc, which the responses are downloaded into an excel format of google sheets. In one of the cells, there are about three product names listed. I am needing to detect if there are mutiple words within the cell, and automatically separate them, while copying all of the other information in 5 other cells. The separation needs to be vertical, not horizontal on the spreadsheet. Please advise and thank you in advance.
Hello, Ann,
Looks like there is no simple solution for your task, most likely you need a special VBA macro.
How to separate text and number.
exp.
rakesh400
mukesh401
abhi402
pls
If you always have three numbers to the right, then use these formulas:
Text: =left(A2,len(A2)-3)
Numbers: =right(A2, 3)
Where A is the column with the source data.
Enter the formulas into two columns and copy them down to the end of the source data.
If the number of digits after text can be different, then you need a VBA macro to fulfill this task, the formula will be too long.
Hi,
I would like to know how to split cells horizontally. Is it possible, I know I can do it in a Word document but can't work out if it is possible in Excel.
I have a spreadsheet with each line for columns A to N with a height of 84.0 (112 pixels). From columns O to W I would like to half the height to 42.0 (56 pixels).
Is that something that can be done?
BTW thanks for the above article, I have learnt something I will be able to use.
hi,
would like to ask if i created a file as text file, i open with excel.
all the contents in the sheet is in the same column, may i know how can i make it in separate column as what i read in adobe or our system ?
Ie :
ACCOUNT : 111 AREA : bbb1
NO STOCK CODE DESCRIPTIONS QUANTITY U.PRICE DISC AMOUNT
00000000 CHIC.FLOSS 1KG-ORI(HALAL) 5 37.90 189.50
its look like in different column, but its not. all the content was in the same column and the content is not in order,difficulty for us to edit the content which it suppose to be in separate column.