converting imported numbers

[Guest]

I have imported a text file containing numbers that I need
to change/move the decimal left 2 places i.e I need
convert/change 123 to 1.23, 123456 to 1234.56 etc. for the
entire sheet. Any suggestions?

 

[Norman Harker]

Hi!

Save. Then save your original under a new name so as to have a backup.

Put 100 in a cell
Copy
Select range to be changed
Edit > Paste Special > Divide > OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[John Wilson]

anonymous,

One way......
If all of the numbers are in a particular column (example, Col "A")
Then use this formula in column "B" and copy down:

=IF(LEN(A1)>2,LEFT(A1,LEN(A1)-2)&"."&RIGHT(A1,2),IF(LEN(A1)=1,".0"&A1,"."&A1
))

You could then copy/paste special / values and overwrite
your original column with the results of the formulas.

John

 

[John Wilson]

Norman,

Doh!!!!!
Now why didn't I think of that??
I'll try to remember it for the next time instead of the
elaborate (and possibly useless) formula that I came up with.

Thanks,
John

 

[Guest]

Thanks!! works like a charm