Excel: How to split text in a cell

The unique feature about Microsoft Excel is that data can be formatted in many ways depending on how it is configured. You can have text formatted to be just text, multiple units of the text in a single cell separated by CSV data sets. For instance, you can have a whole host of information in a client database such as names and addressed all in one continuous flow with zero breakers. However, why not have that information neatly categorized in different cells split apart columns? How can you achieve this particular unit of operation in Excel to allow for a more complex range of cell splitting? What are the different ways you can split a cell in excel?

Well don’t stress — in this blog, we will address the multiple ways you can split text in a cell. We will also address some of the prominent issues and resolutions to this problem and find ways to help users achieve the necessary steps to split text in whatever configuration they may choose. We will also go through a list of different resolution points and their application, so users are more aware of ways they need to deal with in order to split text in a cell. Follow the step-by-step process below to see how you can split text in a cell.

  1. Split text using the Data Deliminate method.
  2. Create a CSV file from the data.
  3. Use a VBA script to split the text.

After conducting quite a few methods we found the ones stated above to be the most effective in delivering results without much issue you can split cells of information. You can use all of the methods and see which one applies best or try something more complex like the VBA or pivot script to split texts apart. The methods above mostly use a spacer or breaker to identify blocks of text. The text will split along the breakers such as a space or comma (Individual characters cant be used as breakers as they are not recognised by the Excel program. 

Step by step process – Split cells using the Data Deliminate method

  • Firstly, open an Excel sheet.
  • Select the information you want to split.
  • Now click on “Data”.
  • After that click on “Text to column”.
  • Ensure the option “Delimited” is selected.
  • Select a delimiter preference.
  • Now click on “Next”.
  • Set a column destination and data format.
  • Finally, click on “Finish”.

That’s it — you will have effectively split text apart into another cell. Within a single column, you could want to split a cell into two smaller cells. You can’t do that in Excel, unfortunately. Instead, make a new column adjacent to the one with the cell you wish to divide, then split the cell. You may also divide a cell’s contents into numerous neighbouring cells.

You can however merge cells beneath the ones split to give that particular scenario if necessary. For instance, if you have 1 set of data for a couple of subjects or experiments, you can have it configured in this particular manner.

Step by step process – Create a CSV file from the data

If you have a large number of contacts or are having problems importing your contact list into Constant Contact, double-check the formatting and save your Excel spreadsheet (XLS, XLSX) as a Comma Separated Value file (CSV) instead. CSV files are similar to Excel spreadsheets, with the exception that they remove the extra formatting that might cause import problems.

Step by step guide – Create a CSV file from the data

  • Click File in your Excel spreadsheet.
  • Save the file to your hard drive.
  • Select a location to save your file by clicking Browse.
  • From the “Save as type” drop-down option, choose “CSV.”
  • Save the file.

When you open your CSV file, it will seem just like an Excel spreadsheet, with the exception of any extra formatting. Import your contact list into Constant Contact after saving your Excel file as a CSV. CSV Saves a workbook as a comma-delimited text file for usage on another Windows operating system, ensuring that tabs, line breaks, and other characters are appropriately understood. Only the current sheet is saved.

Step by step process – Use a VBA script to split the text

Visual Basic for Applications is the abbreviation for Visual Basic for Applications. Excel VBA is the programming language used by Microsoft in Excel and other Microsoft Office products such as Word and PowerPoint. All of the Office suite programmes are written in the same programming language. The disadvantage of this technique is that it is not particularly flexible, and the macro will exactly duplicate the user’s input. Recorder macros utilise absolutely referring instead of relative reference by default. IKt denotes that macros created in this manner are extremely difficult to utilise with variables and “smart” procedures. While on a progressive scale you may find this consent however here you only need the VBA script to split a cell of data.

Step by step guide Use a VBA script to split the text

Use “Alt + F11” to open the Visual Basic Editor (VBE).

Click “Insert” on the menu bar.

  • After that click on “Module”.
  • A new pane will appear for the module. Paste in the below code.
  • This code builds the MyArray single-dimensional format.
  • Because the split method utilises a comma as a delimiter, each portion of the text becomes an array element.
  • The counter variable is set to 3 to indicate column C, which will be the first column to display the split data.
  • The code then iterates through each element in the array, inserting the element into each cell. The row and column references are based on n and Count, respectively.
  • In each loop, the variable Count is increased, causing the data to fill across the row and then downwards.
Sub SplitText()

Dim MyArray() As String, Count As Long, i As Variant

For n = 2 To 12
MyArray = Split(Cells(n, 2), “,”)
Count = 3
For Each i In MyArray
Cells(n, Count) = I
Count = Count + 1
Next i
Next n

End Sub

Microsoft has a huge range of pre-defined codes available for quite a few different kinds of applications. The splitting text uses the code shown above, you simply paste the pre-made code in the script panel and proceed ahead.

In depth – Step by step process – Split cells using the Data Deliminate method

In this example you have all this information from a project you recently conducted, you want to split the subject information apart and put it in a more readable format. How can we do this in a manner that dosnt affect the data and gets us the vievabale format we want. Follow the guide below to see how.

Step by step breakdown

  • Firslty, open an Excel sheet.
  • Select the information you want to split.

I have a set of subject tiles called subject 1 and subject 2 and 6 sets of results all need to be split.

  • Now click on “Data”.
  • After that click on “Text to column”.
  • Ensure the option “Delimited” is selected.
  • Select a delimiter preference.

In our example the breaker was a spacebar, the space will disappear in place of the breaker command. As you can see from the preview it looks perfect. We removed the space between subject and the corresponding number. Ensure those elements are split with a dash to avoid confusion.

  • Now click on “Next”.
  • Set a column destination and data format.
  • Finally, click on “Finish”.

Below is what it looks like, you can merge and center the cell which has the text “experiment”.

  • Select the cells show below the one with experiment and the one next to it.
  • Click on the home tab
  • Click on “Merge and center”.

Finally, this is what it looks like after the merger is complete.

That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts