I would like to move numbers from one cell to another, or in some way extract numbers from one cell to another, with the number being removed from its previous cell.

Picture of how i want the data to be presented

CodePudding user response：

Try:

Formula in B1:

=--CONCAT(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),""))

Or, in case you have values like Test1 Test2 123456:

=--LET(a,TEXTSPLIT(A1," "),FILTER(a,ISNUMBER(--a)))

EDIT: The question is not solely about moving numbers but about presenting input without the number and the number seperately. Therefor try:

Formula in C1:

=LET(a,TEXTSPLIT(A1," "),HSTACK(TEXTJOIN(" ",,FILTER(a,ISERROR(--a))),--FILTER(a,ISNUMBER(--a))))

CodePudding user response：

I would suggest to use FILTERXML() to extract numeric nodes.

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[number()=.]"))

• Here "<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>" will construct a valid xml string.
• FILTERXML() will process that xml string and xPath parameter //s[number()=.] will return only numeric nodes.

To know more about FILTERXML() read