Extract multiple numbers from string excel use formula

[petter_hoang]

Please write formula about the result (use excel2010)



Thank you very much

 

[bosco_yip]

Try, formula solution for using Excel 2010 version

In B4, array (CSE) formula copied across right:

=IFERROR(LOOKUP(9^9,MID("@"&$A4,SMALL(IF(ISERR(-MID("@"&$A4,ROW($1:$98),1))*ISNUMBER(-MID("@"&$A4,ROW($2:$99),1)),ROW($2:$99)),COLUMN(A$1)),ROW($1:$9))*1),"")

 

[p45cal]

No good for @petter_hoang , I know, but for more recent versions of Excel:
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1))



And for numeric results add VALUE():
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),VALUE(TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1)))

 

[GraH - Guido]

Power Query would be possible with Excel 2010...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.AddColumn(Source, "Split", each Text.Combine(List.Select(
Text.SplitAny([TEXT], Text.Remove([TEXT], {"0".."9"})),
each _<> ""), ", "))
in
    Split

Would need to add a split to columns.

 

[bosco_yip]

@herofox @deciog @petter_hoang

Here is a summary of formula solution of Extract multiple numbers from string for difference Excel versions

1] For pre-Excel2010 version:

{=IF(ISERR(SMALL(IF(ISERR(-MID("@"&$A4,ROW($1:$98),1))*ISNUMBER(-MID("@"&$A4,ROW($2:$99),1)),ROW($2:$99)),COLUMN(A$1))),"",LOOKUP(9^9,MID("@"&$A4,SMALL(IF(ISERR(-MID("@"&$A4,ROW($1:$98),1))*ISNUMBER(-MID("@"&$A4,ROW($2:$99),1)),ROW($2:$99)),COLUMN(A$1)),ROW($1:$9))*1))}

2] For Excel2010 version and above:

{=IFERROR(LOOKUP(9^9,MID("@"&$A4,SMALL(IF(ISERR(-MID("@"&$A4,ROW($1:$98),1))*ISNUMBER(-MID("@"&$A1,ROW($2:$99),1)),ROW($2:$99)),COLUMN(A$1)),ROW($1:$9))*1),"")}

3] For Excel2013 version and above:

{=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRIM(CONCAT(IFERROR(--MID($A4,ROW($1:$99),1)," ")))," ","</b><b>")&"</b></a>","//b["&COLUMN(A$1)&"]"),"")}

4] For Excel2021 version:

=TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(TRIM(CONCAT(IFERROR(--MID(A1,SEQUENCE(,LEN(A1)),1)," ")))," ","</b><b>")&"</b></a>","//b"))

4] For Office365 version: ( as per p45cal level#3 solution)

=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1))

 

[deciog]

Bosco, Good morning.

Excellent, I'm learning a lot, this is great

Thank you very much a big hug

Decio

 

[Peter Bartholomew]

No good for @petter_hoang , I know, but for more recent versions of Excel:
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1))

View attachment 81449

And for numeric results add VALUE():
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),VALUE(TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1)))

Pascal
Congratulations on your inspired use of TEXTSPLIT!
Following, your formula, I finished with an alternative presentation as a Lambda function

ExtractNumλ
= LAMBDA(txt,
     LET(
        chr, MID(txt, SEQUENCE(,LEN(txt)), 1),
        sep, FILTER(chr,ISERR(chr*1)),
        VALUE(TEXTSPLIT(txt,sep,,TRUE,1))
     )
  );

The worksheet formula is then

= ExtractNumλ(@text)

filled down. Sadly

= MAP(text, ExtractNumλ)

fails with that ridiculous message "Nested arrays are not supported". The answer is a nested array! It is a bit like typing
= 1 + 2
and getting an error "The number 3 is not supported"! To get an array of results I needed

= MapVλ(text, ExtractNumλ)

where

MapVλ
= LAMBDA(arr, fnλ,
     DROP(
        REDUCE("",arr,
            LAMBDA(stk, a, VSTACK(stk, fnλ(a)))
        ),
     1 )
  )

All a lot of messing about but, if I remember where I saved the functions, they are reusable!

 

[shrivallabha]

With O365, you can also use TEXTJOIN and TRIM, MID (usual suspects in parsing) function like below.

=TRIM(MID(SUBSTITUTE(" "&TRIM(TEXTJOIN("",TRUE,IF(ISNUMBER(MID($A4,SEQUENCE(LEN($A4)),1)+0),MID($A4,SEQUENCE(LEN($A4)),1)," ")))," ",REPT(" ",99)),99*COLUMNS($A$1:A1),99))