Removing a plus sign?

All,

I'm pretty average with Excel, so there may be a not too diffictul solution to this issue, but I have turned to a dedicated forum as I simply can't find it...

I have data I've imported into excel. One column was basically numbers, with 5 additional characters at the end of each value. I used the LEFT command to strip those out (yay me and the help file!)

What I'm left with is the numbers. BUT, there is an issue. Every number is preceded either with a - or a + (depending, obviously, on whether the number was negative or positive).

I'd like to just be able to do normal calcuations etc with the numbers, but the + signs are causing obvious grief. I've tried changing the cells to number, to text, to general etc. I also figured I could just do a search and replace, search for a + and replace with nothing, and then convert all the cells to numbers. But that doesn't work. According to the Find item there are no +s in my spreadsheet.

I assume because the + symbol has significance excel tries to treat it differently and that is causing my issue? Is there a way around this?

Oh, and I'm using Excel 2007...

Any help would be greatly appreciated!

Cheers.

I wonder why you get the + sign.
In excel if a + sign occur in all the cell, that mean those cell is in formula mode which is the same as = sign.

Would be better let us see your excel file that contain those + sign

Originally Posted by blastranger

combine with VALUE to make the + or - sign has meaning and format it as number (positive or negative) :
=VALUE(LEFT(Ax,LEN(Ax)-5))

The + /- DOES have meaning to me, so i tried this second one.

I just get a #VALUE! error.

In case i'm not clear, taking my example above, I start with this

+127,363 RPSE
-2,000 RPSE
for 2 cells.

What i want the result to be is:

127363
-2000
with the cells formatted as number cells.

That way if i add the 2 cells i would get a total of 125363. In other words, I want to be able to do normal equations with positive and negative numbers on that column, which I can't while it is in the format at the top...

Can I do it by doing an IF looking for a +, with a RIGHT command removing that first character when it finds a +?

Hi
you got value error because you just copy paste the formula and not change the required cell.
in this case for example :
+127,363 RPSE is in A1
-2,000 RPSE is in A2

The formula you type in B1 is :
=VALUE(LEFT(A1,LEN(A1)-5))
copy down

now you should be able to perform a normal equation

=SUBSTITUTE(a1,"+","")