Chirag asks:

I want to separate numbers from following text:

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

This is a monster size array formula in cell C5 and it was created in excel 2007. 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:

=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)), "")

How to create an array formula

  1. Select cell C5
  2. Copy (Ctrl + c) above array formula
  3. Paste (Ctrl + v) to cell C5
  4. Press and hold Ctrl + Shift
  5. Press Enter once
  6. Release all keys

How to copy array formula

  1. Select cell C5
  2. Copy (Ctrl +c) cell
  3. Select cell range D5:E5
  4. 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 8 - 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, "")

and returns 123 in cell C5.

Download excel workbook
Separate-numbers-from-a-text-stringv2.xlsx