|
BIG numbers
Hi
I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
How about a custom format of
#,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
Hi Mike
Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
Hi,
Format a completely empty cell with the format I gave you and then enter 123456789 in that cell and it should display as 123,457. If that works and it doesn't work on your numbers then your numbers are probably text. To convert then to numbers use this formula =SUBSTITUTE(A15," ","") Copy the numbers and paste them back over themselves using paste special - values. Finally put a 1 in a cell and copy that. Select these newly created numbers and then Edit|Paste special - Multiply OK Apply the format and you should get the desired result. Mike "Rockbear" wrote: Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
You've probably entered the "numbers" as text, rather than as numbers. If
you are putting spaces in the numbers and these are not what your Windows Regional Options recognise as the thousands separator, then Excel is liable to treat them as text. If you change your Windows Regional Options to match the format you are using for your input, then Data/ Text to Columns will allow you to translate the text into numbers. Alternatively you could use a formula such as =--SUBSTITUTE(A1," ","") As far as formatting the cells, once they are recognised as numbers, Mike's format had a comma as a thousands separator, so if you want a space use # ##0,_) -- David Biddulph "Rockbear" wrote in message ... Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
David,
Unfortunate linewrap. # ##0,_) Mike "David Biddulph" wrote: You've probably entered the "numbers" as text, rather than as numbers. If you are putting spaces in the numbers and these are not what your Windows Regional Options recognise as the thousands separator, then Excel is liable to treat them as text. If you change your Windows Regional Options to match the format you are using for your input, then Data/ Text to Columns will allow you to translate the text into numbers. Alternatively you could use a formula such as =--SUBSTITUTE(A1," ","") As far as formatting the cells, once they are recognised as numbers, Mike's format had a comma as a thousands separator, so if you want a space use # ##0,_) -- David Biddulph "Rockbear" wrote in message ... Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
Agreed.
-- David Biddulph "Mike H" wrote in message ... David, Unfortunate linewrap. # ##0,_) Mike "David Biddulph" wrote: You've probably entered the "numbers" as text, rather than as numbers. If you are putting spaces in the numbers and these are not what your Windows Regional Options recognise as the thousands separator, then Excel is liable to treat them as text. If you change your Windows Regional Options to match the format you are using for your input, then Data/ Text to Columns will allow you to translate the text into numbers. Alternatively you could use a formula such as =--SUBSTITUTE(A1," ","") As far as formatting the cells, once they are recognised as numbers, Mike's format had a comma as a thousands separator, so if you want a space use # ##0,_) -- David Biddulph "Rockbear" wrote in message ... Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
AC 0810
Cumul NOK 197,116,853 -74,281,084 122,835,769 -11,005,577 -10,993,675 0 0 -2,194,051 -333,394 -2,862,694 -1,066,468 -1,990,147 -5,927,555 -690,533 -1,213,536 -281,544 -1,098,468 -168,214 -1,919,818 -3,696,781 -820,149 These are a fewfof the numbers, the , (comma) is separator of the thousend. The numbers are not text because it is punched in by economic dept. All I get is a comma in the end of the number -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
AC 0810
Cumul NOK 197,116,853 -74,281,084 122,835,769 -11,005,577 -10,993,675 0 0 -2,194,051 -333,394 -2,862,694 -1,066,468 -1,990,147 -5,927,555 - 690,533 -1,213,536 -281,544 -1,098,468 -168,214 -1,919,818 -3,696,781 -820,149 These are a few of the numbers, the , (comma) is separator of the thousand. The numbers are not text because it is punched in by economic dept. All I get is a comma in the end of the number -- Just a regular user "David Biddulph" wrote: Agreed. -- David Biddulph "Mike H" wrote in message ... David, Unfortunate linewrap. # ##0,_) Mike "David Biddulph" wrote: You've probably entered the "numbers" as text, rather than as numbers. If you are putting spaces in the numbers and these are not what your Windows Regional Options recognise as the thousands separator, then Excel is liable to treat them as text. If you change your Windows Regional Options to match the format you are using for your input, then Data/ Text to Columns will allow you to translate the text into numbers. Alternatively you could use a formula such as =--SUBSTITUTE(A1," ","") As far as formatting the cells, once they are recognised as numbers, Mike's format had a comma as a thousands separator, so if you want a space use # ##0,_) -- David Biddulph "Rockbear" wrote in message ... Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
BIG numbers
I'd be willing to guess that at least some of them *are* text. Try
=ISTEXT(A2) and =ISNUMBER(A2) and copy down the coluimn. -- David Biddulph "Rockbear" wrote in message ... AC 0810 Cumul NOK 197,116,853 -74,281,084 122,835,769 -11,005,577 -10,993,675 0 0 -2,194,051 -333,394 -2,862,694 -1,066,468 -1,990,147 -5,927,555 - 690,533 -1,213,536 -281,544 -1,098,468 -168,214 -1,919,818 -3,696,781 -820,149 These are a few of the numbers, the , (comma) is separator of the thousand. The numbers are not text because it is punched in by economic dept. All I get is a comma in the end of the number -- Just a regular user "David Biddulph" wrote: Agreed. -- David Biddulph "Mike H" wrote in message ... David, Unfortunate linewrap. # ##0,_) Mike "David Biddulph" wrote: You've probably entered the "numbers" as text, rather than as numbers. If you are putting spaces in the numbers and these are not what your Windows Regional Options recognise as the thousands separator, then Excel is liable to treat them as text. If you change your Windows Regional Options to match the format you are using for your input, then Data/ Text to Columns will allow you to translate the text into numbers. Alternatively you could use a formula such as =--SUBSTITUTE(A1," ","") As far as formatting the cells, once they are recognised as numbers, Mike's format had a comma as a thousands separator, so if you want a space use # ##0,_) -- David Biddulph "Rockbear" wrote in message ... Hi Mike Did not work :) Marked the numbers, format cells, custom format and wrote the #,##0,_) but this did not make the numbers in thousand -- Just a regular user "Mike H" wrote: How about a custom format of #,##0,_) Mike "Rockbear" wrote: Hi I have a column H that has large numbers 11 515 100, 74 500 988, 79 560 533 etc Is there a way to format the numbers in 1000? 11 515 100 in thousand it will be 11 515 74 500 988 in thousand it will be 74 501 79 560 533 in thousand it will be 79 561 Just format. the numbers, no formula -- Just a regular user |
| All times are GMT +1. The time now is 09:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com