Number doesn't want to be a number
 

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


--
achidsey

Number doesn't want to be a number
 

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
--
Gary''s Student


"achidsey" wrote:

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


--
achidsey

Number doesn't want to be a number
 

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


"Gary''s Student" wrote:

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
--
Gary''s Student


"achidsey" wrote:

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


--
achidsey

Number doesn't want to be a number
 

Your welcome
--
Gary''s Student


"achidsey" wrote:

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


"Gary''s Student" wrote:

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
--
Gary''s Student


"achidsey" wrote:

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


--
achidsey

Number doesn't want to be a number
 

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.



Gary''s Student wrote:

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
--
Gary''s Student

"achidsey" wrote:

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


--
achidsey

--

Dave Peterson