reversing a negative value in Excel

[collierd]

Hello

Data is coming through on a number of spreadsheets with negative figures shown as (e.g.) 32- rather than -32
Is there a function capable of reversing this?
Or would I have to create a custom one that removes the - and multiplys by -1 (for example)
This occurs in a number of places so I would like it to be reusable

Thanks

Damian.

 

[SkipVought]

Hi,

"coming through" HOW?

Skip,

_[red][/red]

 

[collierd]

Hello

On a set of Excel spreasheets
I don't know why but the people providing us with this spreadsheet seem to think that that's the correct way to interpret negative numbers
They are aware now but I have to deal with this

Thanks

 

[SkipVought]

The you're stuck with it.

sub ConvertMinus()
'FIRST select the column you want to convert
  dim r as range, a
  for each r in rng
    if r.Value = "" then exit for 'assumes that your range of data has contigulus values
    a = split(r.value,"-")
    if IsNumeric(A(0)) and ubound(A)=1 and A(1)="-" then
      r.value = a(0)*-1
    end if
  next
end sub

Skip,

_[red][/red]

 

[dallen43]

You could also use this. It won't fix it in place but in a new column. This check to see if the field is text (32- is text) and then will convert it.

=IF(TYPE(A7)=2,IF(FIND("-",A7,1)>0,SUBSTITUTE(A7,"-","")*(-1),A7),A7)

Hope this helps.

 

[KenWright]

More info of a similar nature, but some really good stuff on John's site as well:-

http://www.mcgimpsey.com/excel/postfixnegatives.html

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[collierd]

Thanks, i'll have a look at this site
Thanks for the help everybody
I got this working
Skip, i used your method although it didn't like rng so I used Selection
Also, for some reason the if statement didn't work so I replaced it with something that removed the - and *-1 (if - present at end)

Damian.

 

[SkipVought]

You are correct.

Skip,

_[red][/red]