Numbers in a column in different formats.

[Wendy Collins]

Hi
I have been sent a spreadsheet containing a column with numbers displayed in different ways; please see attached spreadsheet. I've tried various things using Format Cells and the Trim formula, in an attempt to get these values looking the same. Please can someone help? It would be really useful to know the reason and how to overcome it for future, rather than just being sent the result, as this is bound to happen again.
Thank you very much in advance!

Wendy Collins

[XOR LX]

Hi,

You have many occurrences of a non-printable character (ASCII code 160) amongst your entries.

If you have a numeric keypad (unfortunately this doesn't work using the numbers at the top of the keyboard) then you can do a Find & Replace on your data with:

Find what: [hold down ALT and press 0160 on the numeric keypad]
Replace with: [leave blank]

If you aren't able to do that, a formula in B1:

=0+SUBSTITUTE(A1,CHAR(160),"")

and copied down, will remove those characters and convert your data to numerical format.

Regards

[samba_ravi]

Use find & replace

select the range
press Ctrl + H
Type "," in Find what and click on replace all

[haripopuri]

Select the cells you want to change the format and run this code.

Please Login or Register  to view this content.