How to split cells in Excel

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.

Before you start

We care about your data. The add-in will back up your worksheet if you select the corresponding option.

How to split cells in Excel by characters

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:
Split Excel cells by chars.
The tool pane will show up:
How to split cells by characters.

  1. Select a cell or column with the records you want to split, and you will see its address in this field.
  2. Click the Expand selection icon to get the whole column selected.
    Note. The tool can split only one column at a time.
  3. The values you want to split may be separated by standard delimiters, such as comma, space, semicolon, and/or line break. Also, you can enter any other character into the Custom field.
  4. If the values to be split are divided by several successive symbols and you want the add-in to process them all as one delimiter, select the Treat consecutive delimiters as one option.
  5. If you want to preserve delimiters in the split records, select the Keep delimiters in resulting cells option and then choose whether you want to have them At the beginning or At the end of the cell contents.

    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

  6. Decide how to split your cells: by columns or by rows:
    Split Excel cells by columns or by rows.
  7. Excel doesn't let you cancel changes made by add-ins, which is why we recommend selecting the Back up this worksheet option.

Сlick Split.

How to split text in Excel by mask

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:
Start Split by Mask.
You will need to make a few adjustments in the tool pane:
How to split by mask in Excel.

  1. Select a cell or column with the records you want to split, and you will see its address in this field.
  2. Use the Expand selection icon to get the whole column selected automatically.
  3. Enter the delimiters to help with splitting cells. Use asterisks (*) to indicate the position of your text.
    Tip. If asterisk is one of the delimiters, type the tilde (~) before a double asterisk: ~**.
  4. Choose whether you want to split cells to columns or to rows.
  5. We recommend keeping the Back up this worksheet option selected to have a safe copy of your worksheet.

Click the Split button.

How to split cells in Excel by strings

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:
Start the Split by Strings tool.

Fine-tune the splitting options in the tool pane:
How to split by Excel string.

  1. Select a cell or column with the records you want to split, and you will see its address in this field.
  2. If you want to select the entire column, click Expand selection.
  3. Enter the strings. If values in the column of interest can be separated by more than one string, enter them each into a new line.
  4. If you want to preserve delimiters in the split records, select the Keep delimiters in resulting cells option and then choose whether you want to have them At the beginning or At the end of the cell contents.

    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

  5. Choose whether you want to split cells to columns or to rows.
  6. Leave Back up this worksheet checked to have a safe copy of your data.

Click Split.

Responses

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.

Hello,
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.
Thank you.

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.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.