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.

## Split Text by Number of Characters in Excel: 8 Easy Ways

Throughout this article, we will discuss **8Â **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

### 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

### 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

### 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

### 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: **How to Split Text in Excel Using Formula

### 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.

### 8. Use MID Function to Separate Text and Numbers in Excel

In this context, you will learn how to use **the MID function** to separate text and numbers. Using the MID function, we can easily split the texts and numbers into two different columns.

**STEPS**:

- Select cell
**D5**. - Insert the given formula.

`=(MID(B5:B8,{1,5},4))`

- Press the
**Enter**key. - We will see the texts and numbers are separated into two different columns.

**Download Practice Workbook**

You can download the practice workbook from here.

## 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.

## Related Articles

- Split Text after a Certain Word in Excel
- How to Split Text by Space with Formula in Excel
- Split First And Last Name in Excel
- How to Separate Two Words in Excel

**<< Go Back to Splitting Text |Â Split in Excel |Â Learn Excel**

Try this method (the simplest):

=(MID(B5:B8,{1,5},4))

Hello

MeniThanks for reaching out and sharing your expertise. I have investigated the formula you have shared and found it very powerful. We can easily separate the texts and numbers with this single formula.

Your suggestion proved effective, and We are genuinely grateful for it. Thank you again!

Regards

Lutfor Rahman ShimantoExcelDemy Team