Question: I have a row of numbers that I need to sum. When I try to sum I get zero (0) when I know there should be a value. The numbers all have a (-) negative sign at the end and an extra space at the end.

How do I convert this to an actual number, remove the extra space to be able to sum up? Do I need to remove the (-) negative sign at the end and move to the front?

Here's the data I'm working with.

[![enter image description here](https://i.stack.imgur.com/hKxqC.jpg)](https://i.stack.imgur.com/hKxqC.jpg)

Answer: 
To find the sum without a helper column use this array formula:

```
=SUM(VALUE(IF(ISNUMBER(SEARCH("-",A1:A6)),"-","")&SUBSTITUTE(A1:A6,"-","")))

```

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put `{}` around the formula.

This will assume any that do not have `-` at the end are positive numbers and sum as such.

[![enter image description here](https://i.stack.imgur.com/ma01e.png)](https://i.stack.imgur.com/ma01e.png)
Another solution assuming that all negative numbers will have `-` followed by  (a space) at the end. Use the following formula:

```
=SUM(VALUE(IF(RIGHT(A1:A6,2)="- ","-"&LEFT(A1:A6,LEN(A1:A6)-2),A1:A6)))

```

This is an array formula so commit it by pressing `Ctrl`+`Shift`+`Enter`.

See image for reference.

[![enter image description here](https://i.stack.imgur.com/HhBHl.png)](https://i.stack.imgur.com/HhBHl.png)