doing my swede this one.

i have list of numbers – costs to be precise all in the format 1.45 etc
Some allow me to change them to currency/number and therefore do a range of counts and sums, some don’t. They just refuse to be treated as numbers despite my best efforts.

the main issue is on selecting certain numbers, they show no count in the bar at the bottom. Cell format is number.

Any tips?

Without looking try selecting the column and doing

Data – text to columns. Then click finish

Copy and paste formatting from the ones that work to the ones that don’t?
(Sorry if you’ve tried this.)

How have the numbers got into the cells? Any errant spaces?

Tried text to columns – nope. Paste format – nope. Thanks for suggestions so far. Like i say – annoying!

Removed blanks – nothing doing. Data imported as copy/paste from pdf or excel csv

Excel used to have a problem if the cells have ever been formatted as text, they won’t reformat properly as numbers. I think the trick was to create a new column and paste the data then delete the old one. If necessary, paste the data somewhere neutral like a Word table, then re-copy.

Bit crude, but inset another column and multiply them each by 1and use the new column

I guess it’s the rows from the pdf that are causing the problem?

Any ‘carriage return’/line break characters? If you select one cell and go into the formula bar is there really one line, or has it got a blank second line within the cell?

Variation on T1000 that doesn’t leave an extra column of data/calcs.

Say your numbers are in cell range B1:B20
There is nothing in column C

Enter a 1 in an otherwise empty cell,lets say C1.

Copy C1

Select B1:B20

Use Paste Special >>> Multiply

This will force them all to numbers

Delete the content of C1.

Right – i think you have collectively sorted it. Thanks all.

Took it all out into an as-neutral program as i could – notepad. Word retained formatting.

Takign that back in, deleting blanks, tidying up – it’s working.

thanks again

🙂

Oh and if it’s come from PDF make sure you’ve unmerged the columns from the first paste.

Could have used =value(A1) to force text to number conversion.