## Extract numbers from a value

**Chirag asks:**

I want to separate numbers from the following text:

Abc123bx45 as a result 123 and 45 should be in different cells.

**Answer:**

This is a monster size array formula in cell C5, the text string length is maximum 20 characters. If you know you will be working with larger text strings, change every instance of ROW($1:$**20**) to ROW($1:$**text_length**).

**Array formula in cell C5:**

**How to create an array formula**

- Select cell C5
- Copy (Ctrl + c) above array formula
- Paste (Ctrl + v) to cell C5
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell C5
- Copy (Ctrl +c) cell
- Select cell range D5:E5
- Paste (Ctrl + v)

### Explaining array formula in cell C5

#### Step 1 - Create an array of each character in cell value

MID(" "&$A5&" ", ROW($1:$20), 1)

becomes

MID(" Abc123bx45 ", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, 1)

becomes

{" ";"A";"b";"c";"1";"2";"3";"b";"x";"4";"5";" ";"";"";"";"";"";"";"";""}

#### Step 2 - Return numeric code for each value in array

CODE(MID(" "&$A5&" ", ROW($1:$20), 1))

becomes

CODE({" ";"A";"b";"c";"1";"2";"3";"b";"x";"4";"5";" ";"";"";"";"";"";"";"";""})

and returns

{32; 65; 98; 99; 49; 50; 51; 98; 120; 52; 53; 32; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}

#### Step 3 - Check if numeric code is more than 57 or less than 48

IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)

becomes

IF({32; 65; 98; 99; 49; 50; 51; 98; 120; 52; 53; 32; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}>57)+({32; 65; 98; 99; 49; 50; 51; 98; 120; 52; 53; 32; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}<49), "", 1)

and returns

{"";"";"";"";1;1;1;"";"";1;1;"";#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

#### Step 4 - Compare each character in text string " "&$A5&" " and $A5&" "

IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), "")

becomes

IF({""; ""; ""; ""; 1; 1; 1; ""; ""; 1; 1; ""; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}<>{""; ""; ""; 1; 1; 1; ""; ""; 1; 1; ""; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, "")

and returns

{""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}

We have now filtered the position of every set of numbers in this text string: " Abc123bx45 ".

#### Step 5 - Remove error values

IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), "")

becomes

IFERROR({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}, "")

and returns

{""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}

#### Step 6 - Filter every other value starting with the smallest value {1, 3, 5 ,...}

SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2-1)+1

becomes

SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, COLUMN(A1)*2-1)+1

becomes

SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 1) +1

and returns 4+1 = 5.

#### Step 7 - Filter every other value starting with second smallest value {2, 4, 6 ,...}

SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, COLUMN(A1)*2)

becomes

SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, 2)

and returns 7.

#### Step 8 - Subtract numbers

SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2)-SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2-1))

becomes

SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, COLUMN(A1)*2)-SMALL({""; ""; ""; 4; ""; ""; 7; ""; 9; ""; 11; ""; ""; ""; ""; ""; ""; ""; ""; ""}, COLUMN(A1)*2-1))

becomes

7-4=3

#### Step 9 - Return the characters from the middle of a text string, given a starting position and length

=IFERROR(MID(" "&$A5&" ", SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2-1)+1, SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2)-SMALL(IFERROR(IF(IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<48), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<48), "", 1), ROW($1:$20), ""), ""), COLUMN(A1)*2-1)), "")

becomes

=IFERROR(MID(" "&$A5&" ", 5, 7-4, "")

becomes

=IFERROR(MID(" Abc123bx45 ", 5, 3, "")

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine [โฆ]

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) [โฆ]

The LEFT function allows you to extract a string from a cell with a specific number of characters, however, if [โฆ]

I this article I will show you how to get numerical values from a cell range manually and using an [โฆ]

How to extract numbers from a cell value

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), [โฆ]

The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B. =TRIM(RIGHT(SUBSTITUTE(B3, " [โฆ]

Extract k-th word in cell value

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, [โฆ]

### 3 Responses to โExtract numbers from a valueโ

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Posted previously in the old newsgroups by Lars-ร ke Aspelin...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 14 digits in the input string.

(Following digits will be shown as zeroes.)

Maybe of no practical use, but it will also handle the following two cases correctly:

- a "0" as the first digit in the input will be shown correctly in the output

- an input without any digits at all will give the empty string as output (rather than 0).

Sorry about my previous post... on looking more carefully, I see it does not do what Chirag asked of you.

This is a Great Website You will probably find Exciting that individuals Encourage A person.