Number doesn't want to be a number

[achidsey]

Excel Experts,

To populate a spreadsheet, I imported a number of fields, incluing Shares
and Position, from a program call Monarch which reads the file of a printed
report and converts it into fields.

The field for Shares is a number and it imported fine.

The field for Position in the report consists of a number, with an "L" or an
"S" at the end, to indicate Long or Short. e.g 1500L, 2000S.

I copied the Morarch table into a spreadsheet, and then stripped off the "L
or the "S" from the Position fields. then I formatted the Position cells as
"Number".

However, the cells from the Position field are still weird. The main
problem is that when I incorporate them in an IF statements, and ask IF
Position = Shares, I get a no, even if the amounts are equal.

I've set the cell format to Number, I've tried Copying and Pasting as
Values, using the Trim function, etc. Nothing seems to work.

The only thing that works is highlighting the cell, pressing the F2 key, and
then <Enter>. That seems to reset it somehow!

Any ideas.

Thanks,
Alan

 

[Gary''s Student]

Put the number 1 in an un-used cell. Then Copy the cell and Paste Special on
top of the stubborn cells with the multiply button checked. This should
convert the bunch of them into true numbers.

Good Luck

 

[achidsey]

Cool solution. better than what I was going to finally do which was to add 1
and subtract 1,then copy as values. Thanks

 

[Gary''s Student]

Your welcome
--
Gary''s Student

Cool solution. better than what I was going to finally do which was to add 1
and subtract 1,then copy as values. Thanks

 

[Dave Peterson]

Another option is to just copy a blank cell and choose add.

You'll have a couple less steps to do and may even notice a difference when you
have a blank cell in the range to convert.