Sum Numbers Ignore Text, All Information in One Cell

[Bosavon]

Hello!
Am having a Cell with (20K Mon, 28K Tue, 38K Wed) and need to sum those numbers without the use of VBA. And The sum Is to cover some blank Cells.. Pls help me how to do it without errors
Thanks in Advance!

 

[hagia_sofia]

Are there always 3 numbers in you data or is there some variance?

 

[Bosavon]

Are there always 3 numbers in you data or is there some variance?

They vary, sometimes with decimals

 

[Tom.Jones]

Hi,

1. 20K = 20000 or 'K' is only letter
2. after number is all time K or could be ...
3. 20+28+38 or 20000+28000+38000

 

[Bosavon]

Hi,

1. 20K = 20000 or 'K' is only letter
2. after number is all time K or could be ...
3. 20+28+38 or 20000+28000+38000

Yes!
K is for thousands and Always follow the numbers

 

[Joe4]

How about this formula (entered with CTRL+SHIFT+ENTER) for values in cell A2:A4:

{=SUM(LEFT(A2:A4,FIND("K ",A2:A4)-1)*1000)}

(Note that the "{" and "}" are not literally there, they are added when you enter the formula with CTRL+SHIFT+ENTER).

 

[Tom.Jones]

How about this formula (entered with CTRL+SHIFT+ENTER) for values in cell A2:A4:

{=SUM(LEFT(A2:A4,FIND("K ",A2:A4)-1)*1000)}

(Note that the "{" and "}" are not literally there, they are added when you enter the formula with CTRL+SHIFT+ENTER).

View attachment 114140

Joe4,

All data are in one cell, not different cells

Am having a Cell with (20K Mon, 28K Tue, 38K Wed).....

 

[Joe4]

Joe4,

All data are in one cell, not different cells

Ah, I missed that subtle detail.
What a horrible data structure to have to try to work with!

I am sure someone may be able to come up with a solution. It is too bad this isn't on 365, where there are many new functions at your disposal to make this a bit easier.
But I am afraid I do not know of a good way to do it without VBA.

 

[Cubist]

Based on your prior thread, you're using 365. Try:

=SUM(--TEXTBEFORE(TEXTSPLIT(A2,","),"K"))

 

[Joe4]

Based on your prior thread, you're using 365.

If that is indeed the case, they need to update their account details.
They just registered 3 days ago, and marked that they are using 2013!

Please update your account details if that is really the case!