| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif number is greater than a number but less than another number | Excel Discussion (Misc queries) | |||
| How can I click on a telephone number in an Excel 2002 spreadsheet, and have the number dialed? | Excel Discussion (Misc queries) | |||
| How to calculate number of occurencies of a specific number number | Excel Discussion (Misc queries) | |||
| Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Hybrid Mode
