Mukesh Dipto
This tutorial demonstrates how to split text by a number of characters in excel. When working with a dataset in Excel, the format can vary depending on the source. If cells of a dataset contain multiple types of information then it is considered disorganized data. It becomes so hard to find any type of information from that dataset. To mitigate this hassle in this article, we will split the text by a number of characters.
Download Practice Workbook
You can download the practice workbook from here.
7 Easy Ways to Split Text by Number of Characters in Excel
Throughout this article, we will discuss 7 easy ways to split text by a number of characters in Excel. To illustrate all the methods, we will use the following dataset. The dataset contains codes of different products. The codes consist of 4 numeric characters and 5 alphabetic characters. In this article, we will split the text after 4 characters of the original string.
1. Split Text in Excel by Number of Characters with ‘Power Query’
First and foremost, we will use the Power Query feature to split text in excel by a number of characters. To illustrate this we will use the following dataset.
Let’s see the steps to perform this method.
STEPS:
- Firstly, select any cell from the data range.
- Secondly, go to the Data Select the option ‘From Table/Range’ from the ribbon.
- The above command opens a new dialogue box named ‘Create Table’.
- Thirdly, check the option ‘My table has headers’ and click on OK.
- So, we can see the new Power Query window.
- Next, select a cell from the data.
- Then, select Home > Split Column > By Number of Characters
- Moreover, enter the value 4 in the ‘Number of characters’ textbox.
- Furthermore, check the Split option ‘Once, as far left as possible’.
- Now, click on OK.
- We get results like the image below.
- After that, go to the File tab and select ‘Close and Load’.
- Lastly, we can see the following result in a new worksheet.
NOTE:
In this procedure we have 3 split options:
- Once, as far left as possible: Counts the characters of the first split column from the left and the second split column from the right.
- Once, as far right as possible: Enumerate the characters of the first split column from the right and the second split column from the left.
- Repeatedly: Split the original column into multiple columns dependent on the number of characters. If the initial column has 20 characters and the number of characters is set to 4, we’ll get 5 new columns, each with 5.
Read More: How to Split Text in Excel by Character (5 Quick Methods)
2. Use ‘Text to Column’ Feature to Split Text in Excel by Number of Characters
In this method, we will use the ‘Text to Column’ feature from the Data tab to separate text by the number of characters in Excel. Follow the below steps to perform this method.
STEPS:
- First, select cell (B5:B8).
- Next, go to the Data Select the option ‘Text to Columns’ from the ribbon.
- Then, select the option Fixed width from the ‘Convert Text to Columns Wizard’ window and click on the Next button.
- Afterward, from the ‘Convert Text to Columns Wizard’ click at the desired position from where we want to split the data.
- Now, click on the Next button.
- After that, select the option General and click on the Finish button.
- Finally, we get results like the image below.
Read More: How to Split Text in Excel into Multiple Rows (6 Quick Tricks)
3. Apply LEFT & FIND Functions to Split Text by Number of Characters
In the third method, we will apply the LEFT and FIND functions to separate text by a number of characters in Excel. In Microsoft Excel, the LEFT function returns the text string’s leftmost characters. The FIND function in excel finds the position of a specific character in a text string. To do this method we will follow the below steps.
STEPS:
- To begin with, select cell C5.
- In addition, type the following formula in that cell:
=LEFT(B5,FIND("B",B5)-1)- Furthermore, press Enter.
- So, in cell C5, we get only the numeric part from the value of cell B5.
- After that, type the corresponding formulas for cells C6, C7, and C8 like the following image.
- As a result, we get the values of the numeric part like the image below.
🔎 How Does the Formula Work?
- FIND(“B”,B5): This part returns the position of character B in text string B5. The return value is 5.
- LEFT(B5,FIND(“B”,B5)-1): Here the LEFT function returns characters from the string of cell B5 up to the 5th.
Read More: Split String by Character in Excel (6 suitable Ways)
4. Text Splitting by Number of Characters with LEFT & SEARCH Functions
In this method, we will use the combination of LEFT and SEARCH functions to split text by a number of characters in Excel. The SEARCH function is similar to the FIND function. It also returns the position of a specific string from a text string. Follow the below simple steps to perform this method.
STEPS:
- In the beginning, select cell C5.
- Next, Insert the following formula in that cell:
=LEFT(B5,SEARCH("B",B5)-1)- Then, press Enter.
- In cell C5, we get only the numeric part from the value of cell B5.
- Furthermore, for cells C6, C7, and C8, type the relevant formulas as shown in the image.
- Finally, we get results like the image below.
🔎 How Does the Formula Work?
- SEARCH(“B”,B5): The position of character B in text string B5 is returned in this section. The value returned is 5.
- LEFT(B5,SEARCH(“B”,B5)-1): Here the LEFT function returns characters from the string of cell B5 up to the 5th character.
5. Combine Excel RIGHT and LEN Functions to Split Text
In this method, we will split text by the number of characters combining the RIGHT and LEN functions. We can use the RIGHT function to extract the last characters from a text string. On the other hand, the LEN function in Excel calculates the length of a text string in characters. Let’s see the steps to apply these two functions.
STEPS:
- Firstly, select cell C5.
- Secondly, write down the following formula in that cell:
=RIGHT(B5,LEN(B5)-4)- Press, Enter.
- So, in cell C5, we get the value of the text part of cell B5. It returns 5 characters from the right end of the original text string.
- After that, drag the Fill Handle tool from cell C5 to C8.
- Finally, we can see the result in the image below.
🔎 How Does the Formula Work?
- LEN(B5)-4: This section returns 4 characters less than cell B5’s initial character number.
- RIGHT(B5,LEN(B5)-4): Here the RIGHT function returns the characters from the right end of the string.
Read More: How to Split String by Length in Excel (8 Ways)
6. Separate Text by Number of Characters Using Flash Fill in Excel
In this section, we will split text by a number of characters using Flash Fill. As we know we want to extract 4 characters from the text strings. To do this we will follow the below steps.
STEPS:
- First, select cell C5.
- Next, enter our desired value manually in that cell.
- Then, in cell C6 start typing the desired value. When we type a digit we can see that Excel gives us an overview of all our desired outputs.
- Now, press Enter.
- Lastly, we can see the result in the following image.
Read More: Splitting Text in Excel Using Flash Fill
7. Excel FILTERXML Functions to Separate Text in Excel
In the last method, we will show you the use of the FILTERXML function to split text by a number of characters. The above function is only available in Microsoft Excel 365. The FILTERXML function returns the output as a dynamic array. Let’s consider splitting the numeric part and alphabetic part from the string values of codes. Just follow the below steps to do this.
STEPS:
- Firstly, select cell C5.
- Secondly, type the following formula in that cell:
=TRANSPOSE(FILTERXML("<s>" &SUBSTITUTE(B5,"-","</s><s>") & "</s>","//s"))
- Press Enter.
- So, we can see that the numeric part and text part is split by the 5th.
- Thirdly drag the Fill Handle tool from cell C5 to C8.
- Finally, we can see the result in the following image.
🔎 How Does the Formula Work?
- FILTERXML(“<t><s>” &SUBSTITUTE(B5,”-“,”</s><s>”) & “</s></t>”,”//s”): By replacing the separator characters to XML tags, the text strings will be converted to XML string.
- TRANSPOSE(FILTERXML(“<t><s>” &SUBSTITUTE(B5,”-“,”</s><s>”) & “</s></t>”,”//s”)): Instead of returning the result vertically, the TRANSPOSE function returns it horizontally.
Read More: How to Split a String into an Array in VBA (3 Ways)
Conclusion
In conclusion, this article is a guide to splitting text by a number of characters in Excel. To put your skills to the test, use the sample worksheet provided in this article. Please leave a comment in the box below if you have any questions. Our team will try to respond to your message as quickly as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.
