Extract numbers - exact values?

[ljubo_gr]

Hello dear MrExcel's

How to extract value from right, from an aray of data?

CMR: 30.501 litres, rest 11.000 for Barack Obama.

Cmr:31.700L but 5000 goes from R-7 to John Kerry.

cmr: 30000 L rest goes 500 to WhiteHouse from R-7

<tbody>
</tbody>

tHESE are only three examples, three rows of data, they are different but simmilar, How to extract 11000 5000 and 500 and Sum that?
This is impossible 'cause they are different, one start with "rest 11000", second starts with "but", third with "goes"....If i extract numbers i get 3050111000 3170050007......
Any help would be kindly appreciated, Thanks!

 

[Peter_SSs]

I just need to: hhmmm My number will always be between CMR:30000litres (30000) and R-7(5to12 designations), so, first i have to find Left that CMR number, then right if exist that R- number, in between is my number. If there isn't R- number then my number will always be smaller than big CMR number. So formula must be so complex. I'm using excel2013. Thank you so much for your timE! I forgot, space" " is between numbers, always.

For a formula solution, based on your samples where "cmr:" is the first thing in the cell, you could try this, copied down. It should also work for decimal numbers (I'm assuming that your decimal separator is ",").

Excel Workbook
	A	B
1	CMR: 30.501 litres, rest 11.000 for Barack Obama.	11000
2	Cmr:31.700L but 5000 goes from R-7 to John Kerry.	5000
3	cmr: 30000 L rest goes 500 to WhiteHouse from R-7	500
Extract number (1)

If "cmr:" may or may not be first in the cell, then try this modification.

Excel Workbook
	A	B
1	some text CMR: 30.501 litres, rest 11.000 for Barack Obama.	11000
2	Cmr:31.700L but 5000 goes from R-7 to John Kerry.	5000
3	some other text incl numbers 101 then cmr: 30000 L rest goes 500 to WhiteHouse from R-7	500
Extract number (2)

 

[ljubo_gr]

THANK YOU SOOO MUCH Peter_SSs and pgc01, I'm unable to test it now, i'll do it later. Me Like Peter's solution, pgc01 where to put code, in ThisWorkbook or Module? My workbook is couple of mebibytes .xlsm file with dozens of Module inside, so, i'll go with Peter's solution. Final question Peter, is it possible single cell formula? Thanks in advance!

 

[Peter_SSs]

Final question Peter, is it possible single cell formula?

I'm not sure what you are asking?

 

[ljubo_gr]

I'm not sure what you are asking?

I meant like: {IF(SUM(LEN(A1:A10......, single cell formula.

=SUMPRODUCT(SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(A1:A10,1,AGGREGATE(15,6,FIND........

 

[ljubo_gr]

Also, i may have problems. When is: CMR:31000L, rest in R-7 or when is: CMR: 31000L rest goes 11000L for Barack
First example is without "my number" 11000, second example is when someone put L at the end of number that will cause problem.

 

[Peter_SSs]

I meant like: {IF(SUM(LEN(A1:A10......, single cell formula.

=SUMPRODUCT(SUBSTITUTE(LEFT(SUBSTITUTE(REPLACE(A1:A10,1,AGGREGATE(15,6,FIND........

Giving half-formulas does not explain what you are trying to do.
Sample data and expected result with explanation would be better.

Also, i may have problems. ... when is: CMR: 31000L rest goes 11000L for Barack
.... when someone put L at the end of number that will cause problem.

It will, because you previously stated:

... space" " is between numbers, always.

 

[ljubo_gr]

Could we change part: FIND({" ","L"}....
LEN as example, 'cause ususally LEN( is single cell), thats from another formula
Thank you Peter for your time....

CMR:31000L, rest in R-7 this could work if i add ! at the end CMR:31000L, rest in R-7! or dot.

 

[Peter_SSs]

Still

Giving half-formulas does not explain what you are trying to do.
Sample data and expected result with explanation would be better.

 

[ljubo_gr]

CMR: 30.501 litres, rest 11.000 for Barack Obama.

Cmr:31.700L but 5000 goes from R-7 to John Kerry.

cmr: 30000 L rest goes 500 to WhiteHouse from R-7

<tbody>
</tbody>

Oh, Peter, ...
I have problems on last example, that seven is giving me trouble. When i put dot or exclamation mark at the end then formula is OK. Also, when my number is with "L", then i also have problems. Is there a way to extract NOTHING when is: CMR12345, some text letter-5to12 Extract MY NUMBER when is CMR12345 some text 11000 some text letter-5to12


PLEASE, MODERATOR RENAME THE TITLE OF THIS THREAD, MY ENGLISH SUCKS

 

[Peter_SSs]

Is there a way to extract NOTHING when is: CMR12345, some text letter-5to12 Extract MY NUMBER when is CMR12345 some text 11000 some text letter-5to12

Are we talking about the original problem in the thread or are we talking about this, which still hasn't been explained clearly:

.. is it possible single cell formula?

Also, does every cell start with "CMR" like Extract number (1) in post #11, or can the cells be like Extract number (2) in that post?