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!

 

[Tom.Jones]

Based on your prior thread, you're using 365. Try:
=SUM(--TEXTBEFORE(TEXTSPLIT(A2,","),"K"))

Op use 2013 .... Joe4 already tell you, but....
For numbers with decimals your formula fails.

They vary, sometimes with decimals

 

[Cubist]

Op use 2013 .... Joe4 already tell you, but....

I'm basing this on the prior thread where the OP uses TEXTBEFORE.

For numbers with decimals your formula fails.

I don't see how but it isn't meaningful to discuss 365 if the OP doesn't have it. Anyhow, do you have an example where it would fail?

 

[Tom.Jones]

20,8K Mon, 28,5K Tue, 38K Wed

20.8K Mon, 28.5K Tue, 38K Wed

 

[Cubist]

I interpreted the OP's "They vary, sometimes with decimals" as the second case, not commas as thousands of separators.

Book1
	A	B
1	20.8K Mon, 28.5K Tue, 38K Wed	87.3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUM(--TEXTBEFORE(TEXTSPLIT(A1,","),"K"))

This should work for both cases.

=SUM(--(SUBSTITUTE(TEXTBEFORE(TEXTSPLIT(SUBSTITUTE(A2,", ","|"),"|"),"K"),",",".")))

 

[Bosavon]

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

Thanks!
But my Data are in the same cell, only comma separated

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!

View attachment 114142

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

Oh!
That first thread was on Android where I have office 365, but on PC O have 2013 where that TextBefore function isn't recognized.

 

[Bosavon]

20,8K Mon, 28,5K Tue, 38K Wed

20.8K Mon, 28.5K Tue, 38K Wed

Like this Monday Income was 28K,

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

I think I'll have to restructure the sheet this way, numbers in different cells, then Sum Left..
THANK YOU ALL FOR YOUR HELP!

 

[Cubist]

I'd also recommend putting them into their separate cells, but this seems to work.

Book1
	A	B
1	20.8K Mon, 28.5K Tue, 38K Wed	87.3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND("K",A1)-1) +MID(A1,FIND(",",A1)+1,FIND("K",A1,FIND("K",A1)+1)-FIND(",",A1)-1) +MID(A1,FIND(",",A1,FIND(",",A1)+1)+2,FIND("K",A1,FIND("K",A1,FIND("K",A1)+1)+1)-FIND(",",A1,FIND(",",A1)+1)-2)

 

[Joe4]

Note: When marking a post as the solution, please mark the post originally containing the solution that you used (or will use).
Please do not mark your own post acknowledging some other post is the solution.
You would only mark your own post if you can up with your own solution (and then posted that).

 

[Scott Huish]

The sample shows it in parentheses, is there other text in the cell that we need to be aware of?

(20K Mon, 28K Tue, 38K Wed)

 

[Bosavon]

I'd also recommend putting them into their separate cells, but this seems to work.

Book1
	A	B
1	20.8K Mon, 28.5K Tue, 38K Wed	87.3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND("K",A1)-1) +MID(A1,FIND(",",A1)+1,FIND("K",A1,FIND("K",A1)+1)-FIND(",",A1)-1) +MID(A1,FIND(",",A1,FIND(",",A1)+1)+2,FIND("K",A1,FIND("K",A1,FIND("K",A1)+1)+1)-FIND(",",A1,FIND(",",A1)+1)-2)

This one gets it right without restructuring...