On Friday 26 August 2005 10:19 am, Pere Castañer Sarda wrote:
> Hi all.
>
> I'm having a great problem dealing with the number's format. I'll
> explain to you my situation:
>
> We (at work) are using a private Administration program that export
> the grid component to and excel file format. OPenoffice.org recognize
> the file and it opens it well. The problem is about the format of
> number. As you can see in the sample provided to you, I need to sum
> the fourth column and openoffice can't do it because the number has
> the format like: 4,494.51 where the "," designs thousands separator,
> and "." the decimal separator. Because of this, I need to tell to
> Calc that the number format is that I shown to you in last paragraph
> for calculating a sumatory , but I can't. I've tried without any
> success:
>
> 1-Going to tools/ options/languages and deselecting the check in
> "Same as your regional settings (,)"[My regional settings arethe
> spanish defaults]: "," for decimal separator
> "." Thousand separator
> 2-Going to formating cells/numbers and selecting the code format
> don't do nothing because the exported data doesn't change, only the
> data entered manually from this next moment.
>
> Doing these things in Excel 2003
> --------------------------------------------
> For getting this procedure work in MS Excel we must select the column
> to be and go to DATA/Text in columns/fixed width (next)(next)
> clicking to Advanced and selecting the desired decimal separator and
> thousands separator. Finalizing the asistant you can make a Sum of
> the whole column.
>
> I don't know what to do for obtaining the same result in
> openoffice.org, any hints? ----
> --
> Pere Castañer Sardà
> Servei informàtic DIBOSCH S.L
Is there some way you can get the private Administration program to
export the data in the correct format? (#.###,00)
What I noticed in your sample spreadsheet could be the problem:
every cell entry began with an apostrophe. That made all the cells
appear as text. (You will only notice this apostrophe in the input line
at the top when you select a cell. It does not appear in the cell
itself.)
If this is the problem (and it could be), add one more column (say
column F) that is formated correctly for the numbers you need
(#.###,00). In cell F1 enter
=Value(D1)
Select cell F1 again so that it has a "handle" (little square box at the
lower right corner of the cell). Drag that handle down the column to
include the last row with data in Column D. Create your sum function in
Column F.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
