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
Asked
Active
Viewed 66 times
2 Answers
4
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))))
JvdV
- 70,606
- 8
- 39
- 70
-
Thanks for the contribution! but i do not get the answer i want from this formula. Also i do not want the results in B to appear in A, B should onle contain the letters – eastafricancoder Oct 14 '22 at 14:13
-
Correction; numbers* not letters - b should only contain numbers! – eastafricancoder Oct 14 '22 at 14:23
-
Right I see what you mean. I'll edit the answer – JvdV Oct 14 '22 at 14:39
-
Oki am i doing thi completly wrong i added your formul =LET(a;TEXTSPLIT(A2;" ");HSTACK(TEXTJOIN(" ";;FILTER(a;ISERROR(--a)));--FILTER(a;ISNUMBER(--a))))) to F2 in my worksheet an got a #NAME?-error – eastafricancoder Oct 14 '22 at 17:01
-
@eastafricancoder What version of Excel. The formula provided requires Excel 365 – Ron Rosenfeld Oct 14 '22 at 17:13
-
@RonRosenfeld excuse my late reply. I have the Microsoft 365 excel version MSO (16.0.14326.21092) - so, this should not be the problem? – eastafricancoder Oct 18 '22 at 07:07
-
@eastafricancoder The formula works OK here. I suspect your version may not have the latest functions yet -- possibly `HSTACK`. – Ron Rosenfeld Oct 18 '22 at 16:12
3
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 andxPathparameter//s[number()=.]will return only numeric nodes.
To know more about FILTERXML() read this post by @JvdV.
Harun24hr
- 30,391
- 4
- 21
- 36
-
This will stay a nice way to split text. But since TEXTSPLIT is introduced I think we'll be using that more often. Also because it's easier to write. The regex handling is definitely useful though. Too bad it's only useful for Windows environment. – P.b Oct 14 '22 at 16:51
-
1@P.b That is limitation of `FILTERXML()` but in some case it is really nice. – Harun24hr Oct 15 '22 at 02:12