formattting cells that refer to imported data

[stuck]

I guess this is 'by design' but it seems a bit odd to me.
- use the Get data from text/CSV feature
- a new worksheet holding the imported data, called Sheet1, is added to the start of the workbook
- on Sheet 1, cell A4 holds the value 1.
- on another worksheet in the workbook I can enter =Sheet1!A4 and the cell,as expected, displays the value 1
BUT
no matter what format I apply to the cell (number, currency, whatever) the cell will only display 1, I can't increase the number of dec. places to make it say 1.0 or 1.00. I can't make it show as £1.00.



Ken

[HansV]

That suggests that Excel sees the value as text, not as a number.
Change the formula to

=--Sheet1!A4

[stuck]

Hoisted on my own petard! Yes the 'raw data' is all text but I didn't think that was the problem because one of the things I tried was to change the formatting of the 'raw data' yes I also select the cell and pressed enter to get the formatting apply but I guess I made a mistake because doing that didn't help.

This revised formula does do the trick though

As an aside why does adding '--' to the start of the formula work? I've never seen that trick before.

Ken

[HansV]

A minus sign forces Excel to treat the value as a number (if possible of course). -"1" is evaluated as -1. The second minus sign makes the result positive again.
You can obtain the same result using

=1*Sheet1!A4

or

=Sheet1!A4+0

or

=VALUE(Sheet1!A4)

All these convert numbers-as-text to 'real' numbers.

[stuck]

Ta.

I know about VALUE, I've used that extensively for many years.

Ken