Remove blank space at end of number

[Nivian]

Good day

Please see below. I have table which has numbers which has a space at the end, when trying to autosum the value is 0 due to this space. I cannot remove it 1 by 1. Please assist.

 

[Snakehips]

@Nivian. Every chance that that your data is text. Assuming that you have just spaces and no other special characters in there then maybe along these lines.

MRXLMAY21.xlsm
	C	D	E
1	VALUES	Direct SUM	CONVERTED
2	1234	3725011	1234
3	12444		12444
4	1234555		1234555
5	1234556		1234556
6	1234557		1234557
7	999		999
8	6666		6666
9	20344	SUM	3725011
Sheet3

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(C2:C8))
C9,E9	C9	=SUM(C2:C8)
E2:E8	E2	=IFERROR(1*TRIM(C2),"")

The SUMPRODUCT formula should work directly on your current data.
With a General cell format, the TRIM formula should convert, applied cells, to number.

If it were me, wanting to convert the whole range I would probably use a simple vba macro.
*NB that the XL2B rendering above does not show the spaces I have in the Value column.
Hope that helps.

 

[Alex Blakenburg]

I think you have bigger issues than that. How are you getting the data into Excel ? Where is it coming from initially and in what format ?
Your first column has a full stop as a decimal separator the others use a comma.
(Getting a mixture in the one table is really unusual)
What is your normal Excel setting full stop or comma ?

Your negative numbers have a trailing minus sign.

The best fix would be to change the process by which you are getting the data.
The next non-VBA approach would be to use Text to Columns on each column (unfortunately one column at a time)

Power Query or VBA would be able to do it too.