### Method 1 – Using ‘Power Query’

The following dataset has one column showing codes.

**STEPS:**

- Select any cell from the data range.
- Go to the
**Data.** - Select the option ‘
**From Table/Range**’ from the ribbon.

- The above command opens a new dialogue box named ‘
**Create Table**’. - Check the option ‘
**My table has headers**’ and click on**OK**.

- The new
**Power Query**window opens. - Select a cell from the data.
- Select
**Home**>**Split Column**>**By Number of Characters**

- Enter the value
**4**in the ‘**Number of characters**’ textbox. - Check the
**Split**option ‘**Once, as far left as possible**’. - Click on
**OK**.

- We get results like the image below.

- Go to the
**File**tab and select ‘**Close and Load**’.

- The following result is 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 depending 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

### Method 2 – Using the ‘Text to Column’ Feature

**STEPS:**

- Select a cell range (
**B5:B8**).

- Go to the
**Data.** - Select the option ‘
**Text to Columns**’ from the ribbon.

- Select the option
**Fixed width**from the ‘**Convert Text to Columns Wizard**’ window and click on the**Next**button.

- From the ‘
**Convert Text to Columns Wizard,**’ click on the desired position from where we want to split the data. - Click on the
**Next**button.

- Select the option
**General**and click on the**Finish**button.

- We get results like the image below.

**Read More: **How to Split Text in Excel into Multiple Rows

### Method 3 – Apply LEFT & FIND Functions

**STEPS:**

- Select cell
**C5**. - Enter the following formula in that cell:

`=LEFT(B5,FIND("B",B5)-1)`

- Press
**Enter**. - In cell
**C5**, we get only the numeric part from the value of cell**B5**.

- Enter the corresponding formulas for cells
**C6**,**C7**, and**C8,**like the following image. - 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

### Method 4 – Using LEFT & SEARCH Functions

**STEPS:**

- Select cell
**C5**. - Enter the following formula in that cell:

`=LEFT(B5,SEARCH("B",B5)-1)`

- Press
**Enter**. - In cell
**C5**, we get the numeric part from the value of cell**B5**.

- For cells
**C6**,**C7**, and**C8**, enter the relevant formulas, as shown in the image. - 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.

### Method 5 – Combining Excel RIGHT and LEN Functions

**STEPS:**

- Select cell
**C5**. - Enter the following formula in that cell:

`=RIGHT(B5,LEN(B5)-4)`

- Press
**Enter**. - 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.

- Drag the
**Fill Handle**tool from cell**C5**to**C8**. - The result is 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

### Method 6 – Using Flash Fill in Excel

**STEPS:**

- Select cell
**C5**. - Enter our desired value in that cell.

- In cell
**C6,**enter the desired value. When we type a digit, we can see that**Excel**gives us an overview of all our desired outputs.

- Press
**Enter**. - The result is in the following image.

**Read More: **How to Split Text in Excel Using Formula

### Method 7 – Using Excel FILTERXML Functions

**STEPS:**

- Select cell
**C5**. - Enter the following formula in that cell:

`=TRANSPOSE(FILTERXML("<s>" &SUBSTITUTE(B5,"-","</s><s>") & "</s>","//s"))`

- Press
**Enter**. - We can see that the numeric part and text part are split by the
**5th**.

- Drag the
**Fill Handle**tool from cell**C5**to**C8**. - The result is 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.

### Method 8 – Using MID Function

**STEPS**:

- Select cell
**D5**. - Enter the following 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 the Practice Workbook**

You can download the practice workbook from here.

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