Extract two sets of numbers between text in cell

[johnbrownbaby]

Hello,

I have the text in a cell:

eFine: 2404852 eFood: 12827

I wanted to extract the numbers and place them in two separate columns:

Test 1	Amp
2404852	12827

I tried this formula

=IF(SUM(LEN(A8)-LEN(SUBSTITUTE(A8, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A8, LARGE(INDEX(ISNUMBER(--MID(A8,ROW(INDIRECT("$1:$"&LEN(A8))),1))* ROW(INDIRECT("$1:$"&LEN(A8))),0), ROW(INDIRECT("$1:$"&LEN(A8))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A8)))/10),"")

but I got all the numbers: 240485212827

Can you help me separate out the numbers to get the table shown above?

Thanks!

 

[Joe4]

Do your entries always follow the format:
WORD - SPACE - NUMBER - SPACE - WORD - SPACE - NUMBER

If they do, then you can use this formula for the first number:

=TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",100)),100,100))

and this one for the second:

=TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",100)),300,100))

 

[JEC]

One formula for office 365

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(SUBSTITUTE(A1,": "," ")," ","</y><y>")&"</y></x>","//y[number()=.]"))

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA script to extract numbers and place in two separate columns - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[johnbrownbaby]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA script to extract numbers and place in two separate columns - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Understood!

Thanks!

 

[johnbrownbaby]

I will bring in that post in this site to here as there is a nuance to that question.