Split Text in a Cell – Explained with Examples

Splitting text in Excel is one of the most common tasks in data manipulation process. Microsoft Excel is renowned for its data analysis capabilities and utility in diverse professional fields, from finance to research. But, beyond its number-crunching prowess, it is also a powerful tool for handling and manipulating text data. Among such features, one of the most helpful ones is the ability to split text in a cell into multiple cells or even rows. In this post, we will explore how to use Excel formulas to split text in a Cell and see some real-time examples.

Split Text in a Cell: Methods

Splitting text in Excel is a very common task. In Excel, there are several different methods to split text in a cell. The best method to use depends on the nature of your data and what exactly you are trying to achieve. Here are a few common methods:

1. Formulas: You can use Excel’s text functions to split text in a cell. The key functions you’ll likely use are LEFT, RIGHT, MID, LEN, FIND, and SEARCH. These functions can be combined in various ways to achieve different results. Formulas are more flexible than the Text to Columns feature, but they can be a bit harder to set up, especially for complex splits.
2. Text to Columns: Excel’s built-in Text to Columns feature is probably the simplest and quickest way to split text in a cell. It’s very straightforward to use and doesn’t require you to write any formulas. However, it’s a little less flexible than some of the other methods.
3. Flash Fill: Introduced in Excel 2013, Flash Fill automatically fills your data when it senses a pattern. For example, if you are splitting a name column into first and last names, you simply type the first name in a separate column, and Excel will automatically fill the rest of the column for you.
4. Power Query: Power Query is a data connection technology that enables you to connect, combine, and refine data across a wide variety of sources. In the context of splitting text, Power Query can be incredibly powerful because it allows you to create advanced splits with multiple delimiters and at varying positions in your text. However, Power Query is a bit more complex and has a steeper learning curve than the other methods.
5. VBA Macros: For the most advanced text splitting scenarios, you might consider using Visual Basic for Applications (VBA) to write a macro to split your text. This requires some programming knowledge, but it allows you to create highly custom splits that wouldn’t be possible with any of the other methods.

Excel provides several methods to split text in a cell, each with its own set of advantages and drawbacks. Your choice of method will depend on your specific needs, your comfort level with Excel, and the complexity of your data.

Excel Formula to Split Text in a Cell

Excel doesn’t have a built-in function to split text, but we can use a combination of Excel’s LEFT, RIGHT, MID, LEN, FIND, SEARCH, and SUBSTITUTE functions to achieve this.

For the purpose of this article, let’s use the following example text: “John-Doe-30”

Splitting Text Using Delimiters:

1. LEFT and FIND: To extract the first name “John”, we can use the LEFT function in combination with FIND. Here’s how:=LEFT(A1, FIND(“-“, A1, 1)-1)In this formula, FIND(“-“, A1, 1) looks for the position of the first “-” in cell A1. The LEFT(A1, FIND(“-“, A1, 1)-1) function then extracts all characters to the left of this position.
2. MID and FIND: To extract the middle name “Doe”, we need to start from the character after the first “-” and stop at the character before the second “-“. This can be achieved using MID and FIND functions.=MID(A1, FIND(“-“, A1) + 1, FIND(“-“, A1, FIND(“-“, A1) + 1) – FIND(“-“, A1) – 1)This formula may seem a bit complex, but it’s all about finding the correct start position and length of the desired text.
3. RIGHT, LEN, and FIND: To get the age “30”, we can use RIGHT, LEN and FIND. This formula finds the position of the last “-” and extracts everything to the right of it:=RIGHT(A1, LEN(A1) – FIND(“~”, SUBSTITUTE(A1, “-“, “~”, LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”)))))Here, the SUBSTITUTE function replaces the last “-” with a “~”, and the FIND function identifies its position. Then, the RIGHT function extracts everything after this position.

Splitting Text Without Known Delimiters:

Sometimes, you may need to split text without a clear delimiter, for example, separating the first name and last name from “JohnDoe”. For this, we can use a combination of LEFT, RIGHT, and MIN functions.

1. LEFT and MIN: If the first name is always starting with a capital letter, you can use the following formula to extract “John”:=LEFT(A1, MIN(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, A1&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”,2))-1)Here, the FIND function is used with an array of capital letters and the MIN function identifies the position of the first capital letter in the second word.
2. RIGHT and LEN: To extract “Doe”, the following formula can be used:=RIGHT(A1, LEN(A1) – MIN(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”J”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, A1&”ABCDEFGHIJKLMNOPQRSTUVWXYZ”,2)) + 1)The function is similar to the previous one, but the RIGHT function extracts everything after the position of the first capital letter in the second word.

Excel may be famous for its numeric data manipulation, but with a little understanding of its text handling capabilities, you can also manipulate text in a wide variety of ways. The methods mentioned in this article just scratch the surface. With the versatile Excel functions like LEFT, RIGHT, MID, FIND, SEARCH, LEN, and SUBSTITUTE, you can effectively handle and manipulate text in Excel cells. Happy Excelling!

Example Formula:

Let’s say you have a two-word string like “John Doe” in cell A1 and you want to split this into two separate cells.

• To extract the first word “John”, use the formula:=LEFT(A1, FIND(” “, A1) – 1)This formula locates the position of the first space, and then uses that position to extract all the characters to the left of the space.
• To extract the second word “Doe”, you can use the following formula:=MID(A1, FIND(” “, A1) + 1, LEN(A1))This formula finds the position of the space, and extracts every character to the right of it.

Splitting a String into Multiple Columns using Convert Text to Columns Wizard

In a situation where you have more than two words, you may need to use Excel’s “Text to Columns” feature:

1. Select the cell or column that contains the text you want to split.
2. Go to the “Data” tab in the ribbon.
3. In the “Data Tools” group, click “Text to Columns”. This opens the “Convert Text to Columns Wizard”.
4. Select “Delimited”, and then click “Next”.
5. Select “Space” as your delimiter.
6. Click “Next” then “Finish” to close the wizard.

Remember, these methods work well when you have a uniform structure across your cells, i.e., every cell has text separated by a single space. For more complex scenarios, you may need to use more complex formulas or possibly consider using Excel’s Power Query feature, which offers advanced text manipulation functions.

VBA Code to Split Text in a Cell

If you have more complex splitting requirements or want to automate the splitting process across multiple cells or worksheets, using VBA (Visual Basic for Applications) could be an excellent solution. Here is an example of how to use VBA to split text in a cell:

Let’s consider that we have the text “John-Doe-30” in cell A1 and we want to split this into different cells.

Sub SplitText()

Dim Text As String
Dim TextParts() As String
Dim i As Integer

' Get the text from cell A1
Text = Range("A1").Value

' Split the text based on the delimiter (in this case, "-")
TextParts = Split(Text, "-")

' Write each part of the text to a separate cell
For i = LBound(TextParts) To UBound(TextParts)
Cells(1, i + 2).Value = TextParts(i)
Next i
End Sub

This code first fetches the text from cell A1 and then splits it using the “-” delimiter. It then writes each part of the split text to a separate cell in the same row, starting from column B (which is denoted by ‘i + 2’ as ‘i’ starts from 0).

You can adjust this code to suit your needs by changing the cell from which the original text is fetched, the delimiter used to split the text, and the location where the split text is written.

To run the VBA code:

1. Press ALT + F11 to open the VBA editor.
2. From the menu, go to Insert > Module. This creates a new module.
3. Copy and paste the code into the module.
4. Close the VBA editor.
5. Run the macro by pressing ALT + F8, select SplitText, and then click Run.

Remember to save your workbook as a Macro-Enabled Workbook (.xlsm) if you want the macro to be available the next time you open the workbook.

vba to split text in excel cell into multiple cells

Here is a simple VBA macro that splits the text in an Excel cell into multiple cells. Let’s say we have a situation where we have text in cell A1, and we want to split this text into multiple cells in the same row using space as a delimiter. Here is the VBA code to accomplish this:

Sub SplitText()

Dim OriginalText As String
Dim TextParts() As String
Dim i As Integer

' Get the original text from cell A1
OriginalText = Range("A1").Value

' Split the text based on the delimiter (in this case, space)
TextParts = Split(OriginalText, " ")

' Loop through each part of the split text and write it to a separate cell in the same row
For i = LBound(TextParts) To UBound(TextParts)
Range("A1").Offset(0, i + 1).Value = TextParts(i)
Next i

End Sub

In this code, Range(“A1”).Offset(0, i + 1) specifies the cell where each part of the split text is written. Offset(0, i + 1) moves zero rows and i + 1 columns from cell A1. For example, if i = 0, this will be cell B1 (Offset(0, 1)), if i = 1, this will be cell C1 (Offset(0, 2)), and so on.

To run the VBA code:

1. Press ALT + F11 to open the VBA editor.
2. From the menu, go to Insert > Module. This creates a new module.
3. Copy and paste the code into the module.
4. Close the VBA editor.
5. Run the macro by pressing ALT + F8, select SplitText, and then click Run.

Remember to save your workbook as a Macro-Enabled Workbook (.xlsm) if you want the macro to be available the next time you open the workbook.

Realtime examples of splitting cells in excel

Excel’s ability to split cells can be incredibly useful in a wide range of real-world scenarios. Here are the top 20 examples of when you might need to split cells in Excel. We can split text to separate numbers, by space and any other delimiter.

• Separating First and Last Names:

A common use for Excel’s cell splitting capabilities is separating a full name into first and last names.

Username: =LEFT(A1, SEARCH(“@”,A1)-1)
Domain: =MID(A1, SEARCH(“@”,A1)+1, 100)

• Splitting Email Addresses:

If you have a list of email addresses, you might want to split the usernames and domain names into separate cells.

Username: =LEFT(A1, SEARCH(“@”,A1)-1)
Domain: =MID(A1, SEARCH(“@”,A1)+1, 100)

• Dividing Street Addresses:

Street addresses often include a number, street name, and street type (e.g., “123 Main Street”). You may need to split these into separate cells.

Street number: =LEFT(A1, SEARCH(” “, A1)-1)
Street name: =MID(A1, SEARCH(” “, A1)+1, 100)

• Separating City, State, and Zip:

If you have a column that includes city, state, and zip code together, you might want to separate them into individual columns.

City: =LEFT(A1, SEARCH(“,”,A1)-1)
State: =MID(A1, SEARCH(“,”,A1)+2, SEARCH(” “, MID(A1, SEARCH(“,”,A1)+2, 100))-1)
Zip: =RIGHT(A1, LEN(A1) – SEARCH(” “, A1, SEARCH(“,”, A1)+2))

• Dividing Product Codes:

If product codes in your inventory include both letters and numbers, you might need to separate these into different cells.

Letters: =LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″))-1)
Numbers: =MID(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″)), 100)

• Extracting Area Codes:

If you have a list of phone numbers, you might want to extract the area code into a separate cell.

Area code: =MID(A1, 2, 3)

• Splitting Dates and Times:

If a cell includes both the date and time, you might want to split these into separate cells.

Date: =INT(A1)
Time: =MOD(A1, 1)

• Separating Units from Values:

If you have a column that includes measurements (e.g., “12 kg”), you might want to separate the numeric value and the unit.

Value: =LEFT(A1, SEARCH(” “, A1)-1)
Units: =MID(A1, SEARCH(” “, A1)+1, 100)

• Splitting URLs:

You might want to divide a URL into the protocol, domain, and path for analysis.

Protocol: =LEFT(A1, SEARCH(“://”,A1)-1)
Domain: =MID(A1, SEARCH(“://”,A1)+3, SEARCH(“/”, MID(A1, SEARCH(“://”,A1)+3, 100))-1)
Path: =MID(A1, SEARCH(“://”,A1)+3+SEARCH(“/”, MID(A1, SEARCH(“://”,A1)+3, 100)), 100)

• Separating Country Codes from Phone Numbers:

You may need to extract the country code from an international phone number.

Country code: =MID(A1, 2, SEARCH(” “, A1)-2)
Phone number: =MID(A1, SEARCH(” “, A1)+1, 100)

• Dividing SKU Numbers:

If SKU numbers include a product category and an item number, you might want to separate these.

Product category: =LEFT(A1, SEARCH(“-“,A1)-1)
Item number: =MID(A1, SEARCH(“-“,A1)+1, 100)

• Splitting Text and Numbers:

If you have cells that include text and numbers together, you might want to separate these.

Text: =LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″))-1)
Numbers: =MID(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″)), 100)

• Separating Usernames and Domains in User Log:

In IT, a user log may contain domain and username together as domain\username, which may need splitting for auditing.

Domain: =LEFT(A1, SEARCH(“\\”,A1)-1)
Username: =MID(A1, SEARCH(“\\”,A1)+1, 100)

• Separating File Names and Extensions:

If you have a list of file names with their extensions, you might want to separate these into different cells.

File name: =LEFT(A1, SEARCH(“.”,A1,LEN(A1)-5)-1)
Extension: =MID(A1, SEARCH(“.”,A1,LEN(A1)-5)+1, 100)

• Splitting Coordinates:

If you have geographical coordinates, you might need to split latitude and longitude into separate cells.

Latitude: =LEFT(A1, SEARCH(“,”,A1)-1)
Longitude: =MID(A1, SEARCH(“,”,A1)+2, 100)

• Dividing Name Prefixes and Suffixes:

If names include prefixes (e.g., Mr., Dr.) or suffixes (e.g., Jr., III), you may want to separate these.

Prefix: =LEFT(A1, SEARCH(“.”,A1))
Suffix: =MID(A1, SEARCH(“,”,A1)+2, 100)

• Splitting Academic Degrees:

In academic or HR data, you may have names followed by degrees (e.g., John Doe, PhD) that you need to separate.

Name: =LEFT(A1, SEARCH(“,”,A1)-1)
Degree: =MID(A1, SEARCH(“,”,A1)+2, 100)

• Separating Item and Quantity:

In a shopping list, items and their quantities may be in one cell (e.g., “3 Apples”) and need to be separated.

Quantity: =LEFT(A1, SEARCH(” “,A1)-1)
Item: =MID(A1, SEARCH(” “,A1)+1, 100)

• Splitting Author Names in Bibliographic Data:

In a list of references, author names may be in the format “Lastname, Firstname” and need to be separated.

Last name: =LEFT(A1, SEARCH(“,”,A1)-1)
First name: =MID(A1, SEARCH(“,”,A1)+2, 100)

• Dividing Serial Numbers:

Serial numbers or product keys often have groups of characters separated by hyphens, which you may need to split for better readability or analysis.

Part 1: =MID(A1, 1, SEARCH(“-“,A1)-1)
Part 2: =MID(A1, SEARCH(“-“,A1)+1, SEARCH(“-“,A1, SEARCH(“-“,A1)+1) – SEARCH(“-“,A1)-1)
Part 3: =MID(A1, SEARCH(“-“,A1, SEARCH(“-“,A1)+1)+1, 100)

FAQs: Splitting Text in a cell in Excel

1. How to split text in a cell in Excel?

=LEFT(A1, SEARCH(” “,A1)-1)
=MID(A1, SEARCH(” “,A1)+1, 100)

2. Splitting first and last name in Excel.

First name: =LEFT(A1, SEARCH(” “,A1)-1)
Last name: =MID(A1, SEARCH(” “,A1)+1, 100)

3. Excel formula to split text by space.

First word: =LEFT(A1, SEARCH(” “,A1)-1)
Rest of the string: =MID(A1, SEARCH(” “,A1)+1, 100)

4. Separating numbers and letters in Excel cell.

Text: =LEFT(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″))-1)
Numbers: =MID(A1, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&”0123456789″)), 100)

5. Split text into columns Excel.

You can use the “Text to Columns” feature in Excel under the “Data” tab.

6. How to split a cell in Excel without losing data?

The “Text to Columns” feature, as mentioned above, allows you to split text into separate cells without losing data.

7. Using Excel to split email address into two parts.

Username: =LEFT(A1, SEARCH(“@”,A1)-1)
Domain: =MID(A1, SEARCH(“@”,A1)+1, 100)

8. How to split a cell in Excel 2016 (or other specific versions)?

Use the “Text to Columns” feature available under the “Data” tab in Excel 2016.

9. Excel formula to split text into multiple rows.

This task can’t be performed directly with a formula. You might need to use VBA or Power Query to achieve this.

10. Excel text to columns delimiter.

“Text to Columns” wizard allows you to specify the delimiter (comma, space, tab, etc.) based on which the text should be split.

11. Splitting text in Excel using formula.

Part 1: =LEFT(A1, SEARCH(” “,A1)-1)
Part 2: =MID(A1, SEARCH(” “,A1)+1, 100)

12. How to split cells in Excel using VBA?

VBA solution is more complex and depends on the specific task. Here’s a simple example to split a cell at the first space:

Range(“B1”).Value = Split(Range(“A1″).Value, ” “)(0)
Range(“C1”).Value = Split(Range(“A1″).Value, ” “)(1)

13. Excel MID function to split text.