Number cells won't reformat / sort

[Copyrider]

I'm dealing w/ a spreadsheet composed of cut-and-pasted rows from 2
sources (A and B) both originally exported from Filemaker at different
times.

When trying to sort the data by a "Record Number" field, the 2 datasets
remain split and will not integrate.

In other words, instead of the sort result being something like:

# Code
1 WEX
3 WAF
4 WAF
6 WEX
7 WAF
9 WEX

They look like this:

# Code
1 WEX
6 WEX
9 WEX
3 WAF
4 WAF
7 WAF

The 2 groups remain separate and sort individually.

It gets stranger, though. If I reformat the column to, say, a percent
or a currency rather than a number, only the top group shows the
change. The bottom group does not show the appropriate symbol and
looks as it did before. However, if you focus a cell and call up the
format dialog, its "Number" type will show as percent or currency or
whatever, and the description will mention the correct symbol, but the
sample shows a number without the symbol. Very odd.

This is also true when I try to reformat only the set B cells.

Other columns with "General" and "Text" values sort correctly. Only
this column, which contains exported Filemaker record number values, is
klugey.

The spreadsheets and cells are not locked.

I don't know if it matters that set A was exported from FM7, while set
B was exported from FM8.

Also, I'll have to check w/ IS regarding the script used to export set
B. Does anyone know: If the Filemaker "preserve formatting" box is
checked during export, will that somehow make a cell impervious to
reformatting? If so, is there a manual override option available?

I've tried copying all the data into a new sheet. I've tried copying
the column data into a pre-formatted column. No dice.

One other curious artifact of these exported sets is that borders are
not preserved for empty cells. After saving and closing a worksheet
with this kind of data, upon re-open, borders are invariably missing
from empty cells.

 

[Niek Otten]

Part of your cells are text, although they look like numbers. Remedy:
Format an empty cell as Number. Enter the number 1. Edit>Copy.
Select your "Numbers". Edit>Paste Special, check Multiply. Now they should be numbers and sort correctly.
If not, there are spaces and/or nonprintable characters in your data. You can easily check with the LEN() function; is the length
the same as the number of characters you see? If not, use TRIM() or even TRIMALL() by David McRitchie
(http://www.mvps.org/dmcritchie/excel/join.htm#trimall) to remove those

--
Kind regards,

Niek Otten


| I'm dealing w/ a spreadsheet composed of cut-and-pasted rows from 2
| sources (A and B) both originally exported from Filemaker at different
| times.
|
| When trying to sort the data by a "Record Number" field, the 2 datasets
| remain split and will not integrate.
|
| In other words, instead of the sort result being something like:
|
| # Code
| 1 WEX
| 3 WAF
| 4 WAF
| 6 WEX
| 7 WAF
| 9 WEX
|
| They look like this:
|
| # Code
| 1 WEX
| 6 WEX
| 9 WEX
| 3 WAF
| 4 WAF
| 7 WAF
|
| The 2 groups remain separate and sort individually.
|
| It gets stranger, though. If I reformat the column to, say, a percent
| or a currency rather than a number, only the top group shows the
| change. The bottom group does not show the appropriate symbol and
| looks as it did before. However, if you focus a cell and call up the
| format dialog, its "Number" type will show as percent or currency or
| whatever, and the description will mention the correct symbol, but the
| sample shows a number without the symbol. Very odd.
|
| This is also true when I try to reformat only the set B cells.
|
| Other columns with "General" and "Text" values sort correctly. Only
| this column, which contains exported Filemaker record number values, is
| klugey.
|
| The spreadsheets and cells are not locked.
|
| I don't know if it matters that set A was exported from FM7, while set
| B was exported from FM8.
|
| Also, I'll have to check w/ IS regarding the script used to export set
| B. Does anyone know: If the Filemaker "preserve formatting" box is
| checked during export, will that somehow make a cell impervious to
| reformatting? If so, is there a manual override option available?
|
| I've tried copying all the data into a new sheet. I've tried copying
| the column data into a pre-formatted column. No dice.
|
| One other curious artifact of these exported sets is that borders are
| not preserved for empty cells. After saving and closing a worksheet
| with this kind of data, upon re-open, borders are invariably missing
| from empty cells.
|

 

[Niek Otten]

<Part of your cells are text, although they look like numbers.>

I should have added: Formatting as Number afterwards doesn't help

--
Kind regards,

Niek Otten

| Part of your cells are text, although they look like numbers. Remedy:
| Format an empty cell as Number. Enter the number 1. Edit>Copy.
| Select your "Numbers". Edit>Paste Special, check Multiply. Now they should be numbers and sort correctly.
| If not, there are spaces and/or nonprintable characters in your data. You can easily check with the LEN() function; is the
length
| the same as the number of characters you see? If not, use TRIM() or even TRIMALL() by David McRitchie
| (http://www.mvps.org/dmcritchie/excel/join.htm#trimall) to remove those
|
| --
| Kind regards,
|
| Niek Otten
|
|
|| I'm dealing w/ a spreadsheet composed of cut-and-pasted rows from 2
|| sources (A and B) both originally exported from Filemaker at different
|| times.
||
|| When trying to sort the data by a "Record Number" field, the 2 datasets
|| remain split and will not integrate.
||
|| In other words, instead of the sort result being something like:
||
|| # Code
|| 1 WEX
|| 3 WAF
|| 4 WAF
|| 6 WEX
|| 7 WAF
|| 9 WEX
||
|| They look like this:
||
|| # Code
|| 1 WEX
|| 6 WEX
|| 9 WEX
|| 3 WAF
|| 4 WAF
|| 7 WAF
||
|| The 2 groups remain separate and sort individually.
||
|| It gets stranger, though. If I reformat the column to, say, a percent
|| or a currency rather than a number, only the top group shows the
|| change. The bottom group does not show the appropriate symbol and
|| looks as it did before. However, if you focus a cell and call up the
|| format dialog, its "Number" type will show as percent or currency or
|| whatever, and the description will mention the correct symbol, but the
|| sample shows a number without the symbol. Very odd.
||
|| This is also true when I try to reformat only the set B cells.
||
|| Other columns with "General" and "Text" values sort correctly. Only
|| this column, which contains exported Filemaker record number values, is
|| klugey.
||
|| The spreadsheets and cells are not locked.
||
|| I don't know if it matters that set A was exported from FM7, while set
|| B was exported from FM8.
||
|| Also, I'll have to check w/ IS regarding the script used to export set
|| B. Does anyone know: If the Filemaker "preserve formatting" box is
|| checked during export, will that somehow make a cell impervious to
|| reformatting? If so, is there a manual override option available?
||
|| I've tried copying all the data into a new sheet. I've tried copying
|| the column data into a pre-formatted column. No dice.
||
|| One other curious artifact of these exported sets is that borders are
|| not preserved for empty cells. After saving and closing a worksheet
|| with this kind of data, upon re-open, borders are invariably missing
|| from empty cells.
||
|
|

 

[Copyrider]

Thanks.

We actually discovered a "paste special" fix very similar to this in
the meantime, but it involved adding 0, which also forced a type
conversion from string to num in those fields.

For some reason, the record number field for that FM database is a
string type rather than int.

I still don't know why set A and set B, both exported from FM, were
treated differently. The export script did not include a "preserve
formatting" setting. Perhaps this option was checked in the manual
export of the other set.