Consider the following dataset containing the product codes and sales records of some items. The product codes are created with a combination of the items’ id numbers, their name, and respective years. We will show how to extract the specific numbers from these codes.

## Method 1 – Using Flash Fill Feature to Extract Specific Numbers from an Excel Cell

The **Flash Fill **feature can extract a single number sequence if it follows a pattern throughout the column. Let’s use it for the **ID Number** at the front.

** Steps**:

- Manually write part of the
**ID Numbers**from the B column in the first two cells,**C4**and**C5**.

- Select the third cell,
**C6**. - Go to the
**Data**Tab,**Data Tools**Group, and select the**Flash Fill**option.

- The
**ID No.**column should be filled out with the**ID Numbers**from the**Product Codes**.

## Method 2 – Using Excel LEFT Function to Extract Specific Numbers

Here, we will use the **LEFT function** to extract the **ID Numbers** from the **Product Codes** and the **VALUE function** to convert the extracted strings into numeric values.

** Steps**:

- Type the following function in cell
**C4:**

`=VALUE(LEFT(B4,4))`

Here, **B4 **is the Product Code, and **4 **is for extracting the first four characters from the left. As **LEFT **will extract the specific numbers as text strings, VALUE will convert the extracted strings into numeric values.

- Press
**Enter**and drag down the**Fill Handle**tool.

- You will get the
**ID Numbers**of the products in the**ID No.**column.

## Method 3 – Inserting Excel RIGHT Function to Extract Specific Numbers

Let’s use the **RIGHT function** to extract the **Years **values into the **Year** column.

** Steps**:

- Type the following function in cell
**C4:**

`=VALUE(RIGHT(B4,4))`

B4 is the *Product Code*, and 4 is for extracting the last four characters from the right. RIGHT will bring out the specific numbers as text strings, and VALUE will convert those strings into numeric values.

- Press
**Enter**and drag down the**Fill Handle**tool.

- We get the years from the
**Product Codes**in the**Year**column.

## Method 4 – Using Excel MID Function to Extract Specific Numbers from a Cell

Let’s use the **MID function** to extract the first four numbers of the **Product Codes** into the **ID No.** column.

** Steps**:

- Type the following function in cell
**C4:**

`=VALUE(MID(B4,1,4))`

Here, **B4 **is the ** Product Code**,

**1**is the starting number, and

**4**is for extracting the first four characters from the start position. While

**MID**will extract the specific numbers as text strings,

**VALUE**will convert the extracted strings into numeric values.

- Press
**Enter**and drag down the**Fill Handle**tool.

- The
**ID No.**column will populate.

## Method 5 – Obtaining Specific Numbers from Any Position with Excel FIND Function

For this section, we have rearranged the Product Codes randomly to extract the years from any position of these codes with the help of the **MID function** and **FIND function**.

** Steps**:

- Type the following function in cell
**C4:**

`=VALUE(MID(B4,FIND("2022",B4),4))`

`FIND("2022",B4)`

`becomes`

`FIND("2022","apple18012022") →`

`finds the starting position of`

`2022`

`in`

`apple18012022`

`.`

`Output →`

`10`

`MID(B4,FIND("2022",B4),4)`

`becomes`

`MID(B4,10,4) →`

`extracts`

`4`

`characters with a starting position`

`10`

`Output →`

`2022`

`VALUE(MID(B4,FIND("2022",B4),4))`

`becomes`

`VALUE(2022) →`

`converts the string`

`2022`

`into a numeric value.`

`Output →`

`2022`

- Press
**Enter**and drag down the**Fill Handle**tool.

- We will have the years from the
*Product Codes*in the**Year**column.

## Method 6 – Extracting Specific Numbers from Any Position of a Cell Using SEARCH Function

Like the previous method here we will also search for the specific numbers **2022 **in the randomly created **Product Codes .**

** Steps**:

- Type the following function in cell
**C4**.

`=VALUE(MID(B4,SEARCH("2022",B4),4))`

`SEARCH("2022",B4)`

`becomes`

`SEARCH("2022","apple18012022") →`

`finds the starting position of`

`2022`

`in`

`apple18012022`

`.`

`Output →`

`10`

`MID(B4,SEARCH("2022",B4),4)`

`becomes`

`MID(B4,10,4) →`

`extracts`

`4`

`characters with a starting position`

`10`

`Output →`

`2022`

`VALUE(MID(B4,SEARCH("2022",B4),4))`

`becomes`

`VALUE(2022) →`

`converts the string`

`2022`

`into a numeric value.`

`Output →`

`2022`

- Press
**Enter**and drag down the**Fill Handle**tool.

- The
**Year**column will be filled.

## Method 7 – Extracting Specific Numbers from the End Position of Cells in Excel

We will extract all of the numbers after the text values in the ** Product Codes **with the combination of the

**IFERROR**,

**VALUE**,

**RIGHT**,

**LEN**,

**MAX**,

**IF**,

**ISNUMBER**,

**MID**,

**ROW**,

**INDIRECT**

**functions**.

** Steps**:

- Type the following function in cell
**C4:**

`=IFERROR(VALUE(RIGHT(B4, LEN(B4) - MAX(IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0)))), "")`

`LEN(B4) →`

`gives the length of the total characters in the product code of cell`

`B4`

`.`

`Output →`

`13`

`INDIRECT("1:"&LEN(B4))`

`becomes`

`INDIRECT("1:"&13)`

`INDIRECT("1:13") →`

`gives the reference to this range`

`Output →`

`$1:$13`

`ROW(INDIRECT("1:"&LEN(B4)))`

`becomes`

`ROW(INDIRECT($1:$13) →`

`returns the row numbers serially in this range`

`Output →`

`{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}`

`MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)`

`becomes`

`MID("1801apple2022", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 1) →`

`returns an array of extracted texts for the array of different starting positions.`

`Output →`

`{“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”}`

`MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1`

`becomes`

`MID({“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”})*1 →`

`returns numeric values for the number strings and`

`#VALUE`

`error for the text strings after multiplication with`

`1`

`Output →`

`{1; 8; 0; 1; #VALUE; #VALUE; #VALUE; #VALUE; #VALUE; 2; 0; 2; 2}`

`ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)`

`becomes`

`ISNUMBER({1; 8; 0; 1; #VALUE; #VALUE; #VALUE; #VALUE; #VALUE; 2; 0; 2; 2}) →`

`returns`

`TRUE`

`for the numeric values otherwise`

`FALSE`

`.`

`Output →`

`{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}`

`ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE`

`becomes`

`{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}=FALSE →`

`returns`

`TRUE`

`for`

`FALSE`

`and`

`FALSE`

`for`

`TRUE`

`.`

`Output →`

`{FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE}`

`IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0)`

`becomes`

`IF({FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 0) →`

`returns the numbers from the array for`

`TRUE`

`otherwise`

`FALSE`

`.`

`Output →`

`{0; 0; 0; 0; 5; 6; 7; 8; 9; 0; 0; 0; 0}`

`MAX(IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0))`

`becomes`

`MAX({0; 0; 0; 0; 5; 6; 7; 8; 9; 0; 0; 0; 0}) →`

`returns the maximum number from this range.`

`Output → 9`

`RIGHT(B4, LEN(B4) - MAX(IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0)))`

`becomes`

`RIGHT("1801apple2022", 13 - 9)`

`RIGHT("1801apple2022", 4) →`

`returns the`

`4`

`characters from right.`

`Output →`

`2022`

`VALUE(RIGHT(B4, LEN(B4) - MAX(IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0))))`

`becomes`

`VALUE(2022) →`

`converts the string into a numeric value`

`Output →`

`2022`

`IFERROR(VALUE(RIGHT(B4, LEN(B4) - MAX(IF(ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1)=FALSE, ROW(INDIRECT("1:"&LEN(B4))), 0)))), "")`

`becomes`

`IFERROR(2022, "") →`

`returns a blank for any error`

`Output →`

`2022`

- Press
**Enter**(or**Ctrl + Shift + Enter**if the function doesn’t work) and drag down the**Fill Handle**tool.

- You will get the specific numbers from the end of the cell and can extract any number of values by using this formula.

## Method 8 – Extracting Specific Numbers from Starting Position of Cells

Similarly, we can extract all of the numbers before the text values in the ** Product Codes **with the combination of the

**IFERROR**,

**VALUE**,

**LEFT**,

**LEN**,

**MATCH**,

**IF**,

**ISNUMBER**,

**MID**,

**ROW**,

**INDIRECT**

**functions**.

** Steps**:

- Type the following function in cell
**C4**.

`=IFERROR(VALUE(LEFT(B4, MATCH(FALSE, ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1), 0) -1))," ")`

`LEN(B4) →`

`gives the length of the total characters in the product code of cell`

`B4`

`.`

`Output →`

`13`

`INDIRECT("1:"&13+1)`

`INDIRECT("1:"&14) →`

`gives the reference to this range`

`Output →`

`$1:$14`

`ROW(INDIRECT("1:"&LEN(B4)+1))`

`becomes`

`ROW($1:$14) →`

`returns the row numbers serially in this range`

`Output →`

`{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}`

`MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1)`

`becomes`

`MID("1801apple2022", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14}, 1) →`

`returns an array of extracted texts for the array of different starting positions.`

`Output →`

`{“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”; “ ”}`

`MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)*1`

`becomes`

`MID({“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”; “ ”})*1 →`

`returns numeric values for the number strings and`

`#VALUE`

`error for the text strings after multiplication with`

`1`

`Output →`

`{1; 8; 0; 1; #VALUE; #VALUE; #VALUE; #VALUE; #VALUE; 2; 0; 2; 2; #VALUE}`

`ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1)`

`becomes`

`ISNUMBER({1; 8; 0; 1; #VALUE; #VALUE; #VALUE; #VALUE; #VALUE; 2; 0; 2; 2; #VALUE}) →`

`returns`

`TRUE`

`for the numeric values otherwise`

`FALSE`

`.`

`Output →`

`{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}`

`MATCH(FALSE, ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1), 0)`

`becomes`

`MATCH(FALSE, {TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE}, 0) →`

`returns the position of first`

`FALSE`

`in the array`

`Output →`

`5`

`LEFT(B4, MATCH(FALSE, ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1), 0) -1)`

`becomes`

`LEFT("1801apple2022", 5-1)`

`LEFT("1801apple2022", 4) →`

`returns the`

`4`

`characters from left.`

`Output →`

`1801`

`VALUE(LEFT(B4, MATCH(FALSE, ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1), 0) -1))`

`becomes`

`VALUE(1801) →`

`converts the string into a numeric value`

`Output →`

`1801`

`IFERROR(VALUE(LEFT(B4, MATCH(FALSE, ISNUMBER(MID(B4, ROW(INDIRECT("1:"&LEN(B4)+1)), 1) *1), 0) -1))," ")`

`becomes`

`IFERROR(1801, "") →`

`returns a blank for any error`

`Output →`

`1801`

- Press
**Enter**and drag down the**Fill Handle**tool.

- You will get the specific numbers at the start of the cell and can extract any number of values by using this formula.

For offline versions of Excel, press **Ctrl + Shift + Enter **instead of pressing **Enter**.

## Method 9 – Getting All Numbers from Any Position of Cells in Excel

Here, we will gather all of the numeric values which means the ID numbers and the years together in the **ID No.&Year **column with the help of the **SUMPRODUCT**, **MID**, **LARGE**, **INDEX**, **ISNUMBER**, **ROW**, **INDIRECT**, **LEN **functions.

** Steps**:

- Type the following function in cell
**C4**.

`=SUMPRODUCT(MID(0&B4, LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B4)))/10)`

`0&B4`

`becomes`

`01801apple2022`

`LEN(B4) →`

`gives the length of the total characters in the product code of cell`

`B4`

`.`

`Output →`

`13`

`INDIRECT("1:"&LEN(B4))`

`becomes`

`INDIRECT("1:"&13)`

`INDIRECT("1:13") →`

`gives the reference to this range`

`Output →`

`$1:$13`

`ROW(INDIRECT("1:"&LEN(B4)))`

`becomes`

`ROW(INDIRECT($1:$13) →`

`returns the row numbers serially in this range`

`Output →`

`{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}`

`MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)`

`becomes`

`MID("1801apple2022", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 1) →`

`returns an array of extracted texts for the array of different starting positions.`

`Output →`

`{“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”}`

`ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1))`

`becomes`

`ISNUMBER(--({“1”; “8”; “0”; “1”; “a”; “p”; “p”; “l”; “e”; “2”; “0”; “2”; “2”})) →`

`the double negation converts the numeric strings into numbers and then`

`ISNUMERIC`

`will return`

`TRUE`

`for the numbers.`

`Output →`

`{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}`

`ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4)))`

`becomes`

`{TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}*{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}`

`Output →`

`{1; 2; 3; 4; 0; 0; 0; 0; 0; 10; 11; 12; 13}`

`INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0)`

`becomes`

`INDEX({1; 2; 3; 4; 0; 0; 0; 0; 0; 10; 11; 12; 13}, 0)`

`Output →`

`{1; 2; 3; 4; 0; 0; 0; 0; 0; 10; 11; 12; 13}`

`LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))`

`becomes`

`LARGE({1; 2; 3; 4; 0; 0; 0; 0; 0; 10; 11; 12; 13}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}) →`

`arranges the numbers in the array from large to small values`

`Output →`

`{13; 12; 11; 10; 4; 3; 2; 1; 0; 0; 0; 0; 0}`

`LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1`

`becomes`

`{13; 12; 11; 10; 4; 3; 2; 1; 0; 0; 0; 0; 0}+1`

`Output →`

`{14; 13; 12; 11; 5; 4; 3; 2; 1; 1; 1; 1; 1}`

`MID(0&B4, LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1, 1)`

`becomes`

`MID(01801apple2022, {14; 13; 12; 11; 5; 4; 3; 2; 1; 1; 1; 1; 1}, 1)`

`Output →`

`{“2”, “2”, “0”, “2”, “1”, “0”, “8”, “1”, “0”, “0”, “0”, “0”, “0”}`

`MID(0&B4, LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B4)))`

`becomes`

`{“2”, “2”, “0”, “2”, “1”, “0”, “8”, “1”, “0”, “0”, “0”, “0”, “0”} * 10^{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}`

`Output →`

`{20; 200; 0; 20000; 100000; 0; 80000000; 100000000; 0; 0; 0; 0; 0}`

`MID(0&B4, LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B4)))/10`

`becomes`

`{20; 200; 0; 20000; 100000; 0; 80000000; 100000000; 0; 0; 0; 0; 0}/10`

`Output →`

`{2; 20; 0; 2000; 10000; 0; 8000000; 10000000; 0; 0; 0; 0; 0}`

`SUMPRODUCT(MID(0&B4, LARGE(INDEX(ISNUMBER(--MID(B4, ROW(INDIRECT("1:"&LEN(B4))), 1)) * ROW(INDIRECT("1:"&LEN(B4))), 0), ROW(INDIRECT("1:"&LEN(B4))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B4)))/10)`

`becomes`

`SUMPRODUCT({2; 20; 0; 2000; 10000; 0; 8000000; 10000000; 0; 0; 0; 0; 0})`

`Output →`

`18012022`

- Press
**Enter**and drag down the**Fill Handle**tool.

- We will get the concatenation of our specified numbers.

When using other Excel versions except Microsoft Excel 365, press **Ctrl + Shift + Enter **instead of **Enter**.

## Method 10 – Using Excel’s Convert Text to Columns Wizard to Get Specific Numbers

Let’s **separate the decimal numbers** from the product codes in the **ID No. **column.

** Steps**:

- Select the range.
- Go to the
**Data**tab,**Data Tools**group, and choose the**Text to Columns**option.

- The
**Convert Text to Columns Wizard**will appear. - Click on the
**Fixed width**option and press**Next**in the first step.

- Click on the position where you want the separation (as we want to have the division after the
**ID Numbers,**we have clicked after it) - Press
**Next**.

- In
**Column data format, select General.** - For
**Destination,**put**$C$4.** - Press
**Finish**.

- We should have our desired
*ID Numbers*in the**ID No.**column.

## Method 11 – Using VBA Macro to Extract Specific Numbers from a Cell

In this section, we are going to use a **VBA code **to remove numbers from the string.

** Steps**:

- Go to the
**Developer**tab and select**Visual Basic**.

- The
**Visual Basic Editor**will open up. - Go to the
**Insert**Tab and choose**Module**.

- After that, a
**Module**will be created.

- Copy the following code:

```
Sub gettingspecificnumbers()
Dim number As Range
For Each number In Range("B4:B11")
If InStr(number.Value, "2022") > 0 Then
number.Offset(0, 1).Value = Mid(number.Value, InStr(number.Value, "2022"), 4)
Else
number.Offset(0, 1).Value = ""
End If
Next number
End Sub
```

Here, we have declared the **number** as **Range**, and it will store each value of the cells for the range **B4:B11** within the **FOR loop**. **IF** statement will check whether the values contain a specific portion of ** 2022 **with the help of the

**InStr function**which looks for a partial match.

For matching the criteria, we will extract the portion

**from the strings in the adjacent cells.**

*2022*- Press
**F5**. - In this way, you will get the years in the
**Year**column after the extraction from the codes.

**Download Practice Workbook**

**<< Go Back to Separate Numbers Text | Split | Learn Excel**