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 2022 from the strings in the adjacent cells.
- 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
Get FREE Advanced Excel Exercises with Solutions!