I have a table of public domain data from Nomis (The UK Gov't office
that provides data on employment).

I have created the table manually and it works fine. The Nomis one will
not display as a chart. I have copied the formatting of the manual one
to the Nomis version, so it's not that. All cells are 'general' in both
tables.

If I double click in each cell, it does something to it and it will
then be recognised but I cannot see anything which tells me what the
difference is before and after.

If anyone can help me understand what the issue is, and even better,
how to solve it, I'd appreciate it.

I'm limited to Excel 97 SR2.

Please see example attached.

Many thanks,

Baldy


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Try a =Trim(A1) to remove trailing spaces. These would be detected by
the =Len(A1) formula, and are removed in the trim function.

After the trim, you can Copy - and Paste Special = Values back over the
original data.


BaldySlaphead Wrote:
I have a table of public domain data from Nomis (The UK Gov't office
that provides data on employment).

I have created the table manually and it works fine. The Nomis one will
not display as a chart. I have copied the formatting of the manual one
to the Nomis version, so it's not that. All cells are 'general' in both
tables.

If I double click in each cell, it does something to it and it will
then be recognised but I cannot see anything which tells me what the
difference is before and after.

If anyone can help me understand what the issue is, and even better,
how to solve it, I'd appreciate it.

I'm limited to Excel 97 SR2.

I tried to attach an example, but for some reason it thinks the file is
invalid (I can assure you it's not, and nor does it have macros/viruses
or anything else funny about it.

Many thanks,

Baldy


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Bryan Hessey Wrote:
Try a =Trim(A1) to remove trailing spaces. These would be detected by
the =Len(A1) formula, and are removed in the trim function.

After the trim, you can Copy - and Paste Special = Values back over the
original data.

Hi Brian,

I've tried a =Len(a1) and it does reveal that a sample field is
length=4, the visible field being '30.5'. However, I've done a trim and
tried paste special, values, to no avail. Checking the same field still
reveals a length of '4'.

Argh! Any more ideas?

Cheers,

Baldy


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Addendum - when checking the original, non-functioning cells, there are
no trailing spaces appearing in the formula bar. In order to use the
new paste special values, as discussed above, I still need to click in
each cell so there is an active cursor. At that point, the numbers
align right (initially they appear align left) before they will pick up
for a chart.

Baldy


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Align left indicates text rather than numeric, try =Value(a1)



BaldySlaphead Wrote:
Addendum - when checking the original, non-functioning cells, there are
no trailing spaces appearing in the formula bar. In order to use the
new paste special values, as discussed above, I still need to click in
each cell so there is an active cursor. At that point, the numbers
align right (initially they appear align left) before they will pick up
for a chart.

Baldy


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Bryan Hessey Wrote:
Align left indicates text rather than numeric, try =Value(a1)

That works, Bryan. But how come, even when I previously had changed the
format to numeric, it wouldn't work?

Many thanks for your assistance!

Baldy


--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Changing the cell format often has little effect on existing contents, a
MVP might explain that in more detail, to me it just doesn't apply
except to newly entered data.

Imported data often has problems, ours is with lists imported from a
phone system to be matched to one from Lotus Notes.


Good to see that yours works.



BaldySlaphead Wrote:
That works, Bryan. But how come, even when I previously had changed the
format to numeric, it wouldn't work?

Many thanks for your assistance!

Baldy


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=489748

Changing the format doesn't change the value. But it does prepare it for the
next change (and F2|Enter is enough).

I like this technique:
Select an empty cell
edit|copy
select the range of offending cells
edit|paste special|check add

Excel will coerce the text numbers to number numbers.



BaldySlaphead wrote:

Bryan Hessey Wrote:
Align left indicates text rather than numeric, try =Value(a1)

That works, Bryan. But how come, even when I previously had changed the
format to numeric, it wouldn't work?

Many thanks for your assistance!

Baldy

--
BaldySlaphead
------------------------------------------------------------------------
BaldySlaphead's Profile: http://www.excelforum.com/member.php...fo&userid=1260
View this thread: http://www.excelforum.com/showthread...hreadid=489748

--

Dave Peterson