Problem changing formatting for some cells 1

[NathanGriffiths]

Hi,

I have come up against a strange (to me anyway) problem in an Excel spreadsheet I am trying to import to SQL Server.

When imported, some of the values for cells were NULL although they contained identical values to cells above and below them. I traced the source of the NULL problem to the formatting, so I though I would just be able to reformat all the cells the same to allow a successful import.

My problem is that when I apply formatting to some of the cells it just does not seem to have any effect.

e.g. changing format of a column from Text to Number (2 d.p.) - some of the cells will reformat to Number but some appear to stay as text and there is nothing I can do to force them to change:

Before:
1
1
1
1

After:
1.00
1.00
1 <- this cell does not change!
1.00

The spreadsheet appears to have been created by cutting and pasting bits of other spreadsheets together, so perhaps this is where the problem originates, but can anyone tell me whats going on?

thanks,

Nathan

 

[flareman]

I don't know whether this is your problem but I have noticed that when I format cells as a block, they don't always all change. I'm not quite sure why. (It must have something to do with how much attention I pay to what I'm doing) To make sure that they change I need to copy a cell which I know has the correct formatting and paste it into the block. Perhaps you could give it a try.

David

 

[onedtent]

Chances are, the cell that does not change has trailing spaces. Your best bet is to place a 1 in a blank cell. Higlight the cell,, go to Edit, Copy. Highlight the range that you want to change to numbers go to Edit, Paste Special, choose Multiply and click OK. Format your range to two decimal places.

 

[xlbo]

More likely is that some of what look like numbers ARE numbers and some are text. You can test this by using
=ISNUMBER
or
=ISTEXT

Formatting will NOT change text to a number or vice versa. All formatting does is change what you SEE. It has no effect on the underlying data

To change text to numbers use
=VALUE(A1)
or
=A1*1
and drag down the column then copy the new column and paste as values over the original

to change Numbers to text (2 DPs), use
=TEXT(A1,&quot;0.00&quot
then repeat as above


I would suggest that this is very likely if the spreadsheet has been created from various different data sources

HTH Rgds
Geoff

Veni, vidi, volo in domum redire

 

[NathanGriffiths]

Thanks Geoff, a I have now been able to resolve this using your solution!

cheers,

Nathan

 

[genuineblonde]

On a chart, I have linked to a worksheet cell in a text box. Can I {how} change the alignment,font of the resulting text in box?

 

[xlbo]

By starting a new thread please

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question