The Split Text tool is part of Ultimate Suite for Excel. With its help you can separate a column of text into multiple columns or rows. Easily split data by any character, string, or mask.
We care about your data. The add-in will back up your worksheet if you select the corresponding option.
Use the Split by Characters option if you need to separate data in a column at each occurrence of the delimiters you pick or enter.
Click the Split Text icon on the Ablebits Data tab, in the Text group, and select Split by Characters:
The tool pane will show up:
For instance, if we have "ab.cd.ef" in a cell, split it by "." and do not check this box, we will get ab | cd | ef
If we tick At the beginning, we'll have ab | .cd | .ef
If we tick At the end, the result will be ab. | cd. | ef
Сlick Split.
The Split by Mask option allows you to enter several delimiters which are used within cell contents to separate the necessary values in Excel.
Click the Split Text icon on the Ablebits Data tab, in the Text group, and select Split by Mask:
You will need to make a few adjustments in the tool pane:
Click the Split button.
Use the Split by Strings option to separate values by a combination of characters.
Click the Split Text icon on the Ablebits Data tab, in the Text group, and select Split by Strings:
Fine-tune the splitting options in the tool pane:
For instance, we have "(A)12(a)34(B)56(b)78" in a cell and we split it by the following strings:
(A)
(a)
(B)
(b)
If we do not check this box, we get
12 | 34 | 56 | 78
If we tick At the beginning, we have (A)12 | (a)34 | (B)56 | (b)78
If we tick At the end, the result is 12(a) | 34(B) | 56(b) | 78
Click Split.
Responses
I have data I was able to split from a single cell (comma seperated) into multiple rows successfully using Ablebits Data. The value to the left of the original value I'd like to repeat as well. Is that possible? Example:
Original Data:
Cell 1 Cell 2
Apple Gala, Pink Lady, Opal
After using Ablebits split by row:
Cell 1 Cell 2
Apple Gala
Pink Lady
Opal
What I'd like to see:
Cell 1 Cell 2
Apple Gala
Apple Pink Lady
Apple Opal
Is this possible?
Mike
Hello Mike,
Thank you for contacting us.
It looks like our Split Text may be helpful. You can divide your text not only by comma but also by space and many other characters. Just set the characters you'd like to split your cells by and hit OK.
If you have any questions, do not hesitate to address them to support@ablebits.com. Thank you.
Good Afternoon,
Non of these examples work for splitting a cell. All of the options presented are not in the MS Office Excel 2016.
Hello Nubian-Elon Dupree,
Thank you for your comment. The presented options are part of the Ablebits Text Toolkit add-in for Excel.
You can download the 7-day free trial version here.
It's not working. I just want the opposite of merging 2 cells horizontally, I want to split one.
Absurd this is not a standard feature
Hello Rob,
Thank you for your feedback. Our Split Text tool works perfectly for separating data from one column into the new ones based on a specific separator. If you encountered any difficulties with this feature, please describe the problem you faced in detail and we'll do our best to help you. Thank you.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!