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)
The issue is that the clean and trim functions keep everything as a text, and you have to convert the output of those functions back to a numeric for adding (done by adding 0 to the results of the Len and Trim functions). Here is an array formula that will convert the formula the text and sum it up:

```
=SUM(TRIM(LEFT(A1:A6,LEN(A1:A6)-2))+0)

```

This assumes that your data is in cells A1:A6 and in the format you provided in your post. Remember, it's an array formula and needs to be confirmed with Ctrl+Shift+Enter, and not just enter (if done right, you'll see the curly brackets around the formula...I don't know how to do the fancy icons that people do on stackoverflow). 

I wasn't sure if the negative was part of the number, or just a legacy of the source data. If you want to keep the negative part, you can do something like this as well:

```
=SUM(TRIM(LEFT(A1:A6,LEN(A1:A6)-2))*-1)

```

Again, it's an array formula.