formatting numbers
in [Functions]
|
Prev: How do you calculate rate of return on monthly cash flows
Next: Computing Sales Tax and Retail Price from a number
From: jonas on 16 Feb 2005 04:41 I'm using an internal system in my company where I get the results in excel using som sort of american excel standard. the numbers come out with a space (1000 separator) between numbers (one million is spelled 1 000 000). This is not the 1000 separator you can choose in FORMAT-CELL-NUMBER but some other standard that I can't convert to normal numbers. The format doesn't recognize 1 000 like the number 1000. If i try to sum 1 000 + 1 000 (i.e 2000) i get the result 0 (zero). My problem is that I can't work in this format in my excel 2000 (english). I need to be able to sort the numbers, use them in formulas etc. How do i format the "unreadable" numbers into real numbers i can use.
From: HiArt on 16 Feb 2005 05:15 In a cell: SUBSTITUTE(cell_address, " ","") HTH Art -- HiArt ------------------------------------------------------------------------ HiArt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19953 View this thread: http://www.excelforum.com/showthread.php?threadid=345810
From: jonas on 16 Feb 2005 07:21 Hi Art, Thanks for helping, but I don't understand - I'm not too good with Functions in Excel. Could you clarify how it should look if I have the number 1 234 in cell D4 and want to have the number in a usable format 1234? Where (in what cell) should I be when using SUBSTITUTE? Also; how do I do to make the substitution apply for all cells in a column? What does HTH mean? Is it part of what to do or just "Hope this helps"? // Jonas "HiArt" wrote: > > In a cell:> > SUBSTITUTE(cell_address, " ","") > > HTH> > Art
From: Gord Dibben on 16 Feb 2005 13:12
jonas Select the column of data. Edit>Replace What: space With: enter nothing here Replace all. Gord Dibben Excel MVP On Wed, 16 Feb 2005 01:41:01 -0800, "jonas" <jonas(a)discussions.microsoft.com> wrote: >I'm using an internal system in my company where I get the results in excel >using som sort of american excel standard. the numbers come out with a space >(1000 separator) between numbers (one million is spelled 1 000 000). > >This is not the 1000 separator you can choose in FORMAT-CELL-NUMBER but some >other standard that I can't convert to normal numbers. > >The format doesn't recognize 1 000 like the number 1000. If i try to sum 1 >000 + 1 000 (i.e 2000) i get the result 0 (zero). > >My problem is that I can't work in this format in my excel 2000 (english). I >need to be able to sort the numbers, use them in formulas etc. > >How do i format the "unreadable" numbers into real numbers i can use. |