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
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:
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
I need to convert text to lowercase EXCEPT words that are acronyms. All letters of the ACRONYM must be capitalized, For a simple example, I LIVE IN THE USA - to - I live in the USA. More complicated: MY CLIENT IS PROTECTED BY ERISA - to - My client is protected by ERISA. Thank you.
Thank you for your question.
The Change Case tool doesn't recognize acronyms. As a workaround here we suggest using Replace Substrings. However, first please use Change Case to change all your text to lowercase. Then copy the acronyms to two adjacent columns, and for the second column change case to UPPERCASE. After that you can use the Replace Substrings tool to change the lowercase acronyms in your text to their UPPERCASE equivalents.
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:
Cell 1 Cell 2
Apple Gala, Pink Lady, Opal
After using Ablebits split by row:
Cell 1 Cell 2
What I'd like to see:
Cell 1 Cell 2
Apple Pink Lady
Is this possible?
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 email@example.com. Thank you.
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
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!