Removing the last 2 characters from field in excel 2

[vols77]

For some reason the text file that I get to use in excel has any negative numbers like 37801 - therefore, when I try to add in excel the number is simply skip which is causing all kinds of problems.

Is there away in formula to tell excel to leave off the last 2 characters then multiply by -1?

I tried using =IF(RIGHT(L1,2)=" -",L1-(RIGHT(L1,2)),L1)
but all I get is #VALUE (I think because I am trying to subtract a "-" from a number, but not sure how else to do it.

All help is greatly appreciated.

 

[VulcanJedi]

wouldnt the statement be:
=IF(RIGHT(L1,2)=" -",LEFT(L1,(LEN(L1)-2)), L1)

Tranpkp

 

[AnotherHiggins]

Let me see if I have this straight:

You have some negative numbers that are reading in the following format:
[tab]37801 -
and you want to manipulate them, right?

Are they really numbers that are just formatted like that? It sounds like they are not, but to be sure, please try this.... In another cell, please type in
[tab][COLOR=blue white]=isnumber(L1)[/color]
(replace A1 with the appropriate cell reference)

Does that return true or false?

Assuming it is FALSE, then I'd recommend the following change to tranpkp's formula:
[tab][COLOR=blue white]=[red]VALUE([/red]IF(RIGHT(L1,2)=" -",LEFT(L1,(LEN(L1)-2))[red]*-1[/red], L1)[red])[/red][/color]

The reason being that without wrapping the formula in VALUE, you will still get a text result instead of a number. I also added in *-1 to make the value negative.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[vols77]

That does it, thank you! I thought that "Len" may be involved somewhere but could not think of where...

What can I say.. It's Monday

 

[vols77]

I just saw anotherhiggins's post and I know that will help in a different report I am doing! Right now we are manually haveing to retype numbers and if I understand this right we should be able to use the =value to reformat or turn it into a number?

Really, thank you both, never thought I would get answers this quick - this is the way to start off Monday!

 

[VulcanJedi]

yeah i was gonna ask John's question but yea it was Monday ...so started off slow / simple

Tranpkp

 

[VulcanJedi]

hmm thats an interesting formula anotherhiggins...ill give you a star...guess i just use the native XL right click functionality to reformat strings to numbers as needed but thats good to keep in my back pocket.
I'm just glad I managed to get seomthign to work this AM.

Tranpkp