Separate numbers from a text string
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:
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 49
IF((CODE(MID(" "&$A5&" ", ROW($1:$20), 1))>57)+(CODE(MID(" "&$A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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))<49), "", 1)<>IF((CODE(MID($A5&" ", ROW($1:$20), 1))>57)+(CODE(MID($A5&" ", ROW($1:$20), 1))<49), "", 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 string.xlsx
Related posts:
Search for a text string in an excel table
Filter records within two dates and search for a text string in excel
Search for a text string and return multiple adjacent values
Count occurences of a specific text string in a column in excel
Recommended categories
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.