| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert to number problem
Hi,
I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
Hi Rob
You may be able to convert an entire column in one go by selecting it (the whole column) and going DataTextToColumns and click Finish. Hope this helps! Richard On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
Thanks Richard,
I finally worked out how to fix the complete column in one hit, but my Qs still are, 1. why does the cell format show the cell is formatted as number when it's not! and, 2. why didn't the format painter work? Rob "RichardSchollar" wrote in message oups.com... Hi Rob You may be able to convert an entire column in one go by selecting it (the whole column) and going DataTextToColumns and click Finish. Hope this helps! Richard On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
#1. Changing the format of the cell doesn't change the underlying value. But
the next time you make a change to that value, excel will know that you want it general (or number or...) #2. The format painter did its job fine. But its job is not changing values--just changing format. And as an aside, if you select the all the cells with that triangle warning message, you can convert them all in one fell swoop by just chooing convert to number. (The active cell has to have this triangle warning, though.) Rob wrote: Thanks Richard, I finally worked out how to fix the complete column in one hit, but my Qs still are, 1. why does the cell format show the cell is formatted as number when it's not! and, 2. why didn't the format painter work? Rob "RichardSchollar" wrote in message oups.com... Hi Rob You may be able to convert an entire column in one go by selecting it (the whole column) and going DataTextToColumns and click Finish. Hope this helps! Richard On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
Thanks Dave! Very helpful as always!!
I'm still confused why the values in the cells were behaving as text when the cell format was actually Currency at the time I imported the data. Rob "Dave Peterson" wrote in message ... #1. Changing the format of the cell doesn't change the underlying value. But the next time you make a change to that value, excel will know that you want it general (or number or...) #2. The format painter did its job fine. But its job is not changing values--just changing format. And as an aside, if you select the all the cells with that triangle warning message, you can convert them all in one fell swoop by just chooing convert to number. (The active cell has to have this triangle warning, though.) Rob wrote: Thanks Richard, I finally worked out how to fix the complete column in one hit, but my Qs still are, 1. why does the cell format show the cell is formatted as number when it's not! and, 2. why didn't the format painter work? Rob "RichardSchollar" wrote in message oups.com... Hi Rob You may be able to convert an entire column in one go by selecting it (the whole column) and going DataTextToColumns and click Finish. Hope this helps! Richard On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
I've had this happen to me...
I import a file. One field is imported as text. Then I insert a column to its right. That inserted column is formatted as text. Excel figures that it should use the adjacent column to get the format. But I'm not sure how you could import a file and specify Currency--are you using the text to columns wizard? There's General, Date, Text and skip, right? Rob wrote: Thanks Dave! Very helpful as always!! I'm still confused why the values in the cells were behaving as text when the cell format was actually Currency at the time I imported the data. Rob "Dave Peterson" wrote in message ... #1. Changing the format of the cell doesn't change the underlying value. But the next time you make a change to that value, excel will know that you want it general (or number or...) #2. The format painter did its job fine. But its job is not changing values--just changing format. And as an aside, if you select the all the cells with that triangle warning message, you can convert them all in one fell swoop by just chooing convert to number. (The active cell has to have this triangle warning, though.) Rob wrote: Thanks Richard, I finally worked out how to fix the complete column in one hit, but my Qs still are, 1. why does the cell format show the cell is formatted as number when it's not! and, 2. why didn't the format painter work? Rob "RichardSchollar" wrote in message oups.com... Hi Rob You may be able to convert an entire column in one go by selecting it (the whole column) and going DataTextToColumns and click Finish. Hope this helps! Richard On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
try putting 1 in a blank cell then copy this, select the cells with the
greed tab and editpaste special and select multiply. Delete the cell with the 1 in it. -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Rob" wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert to number problem
Select a cell containing a green triangle and press ctrl+A to select
all cells, then choose convert to number, to apply to all cells on the sheet. On 14 Mar, 05:24, "Rob" <none wrote: Hi, I have pasted some data into a spreadsheet from another accounting application (MYOB) and have trouble having Excel see the amounts as numbers. It seems that even if the cells are formatted as a Numbers or Currency, etc., a simple formula like =SUM(G7:G13) does not see them as numbers. They all have a small green triangle in top left of cell, and when I select a cell and select the option "Convert to Number" it will only then be seen as a number. If I then try to use the format painter to format all the other cells like it, the format painter does nothing. Can someone please tell me what's going on here and a solution, if any, as the workbook will be used often to import new data. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how i convert to arbic number from english number ? | Excel Discussion (Misc queries) | |||
| how do I convert a number to number of years, months and days | Excel Worksheet Functions | |||
| convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
| not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Hybrid Mode
