format of numbers in a downloaed file

[R.VENKATARAMAN]

I have a peculiar (atleast to me) problem,. I get some bills account from my
consultant. This is not in a webpage nor as an email message nor as an
attachment to an email message. I get these bills in the special software
which he has introduced in my computer. But when I < selectall> the entries
in the file and copy to an excel spreadsheet it serves the prupose mostly.
But some numbers are in a strange format neither as numbers, nor as text but
in some format such that I am not able to do calclations on these
"numbers".
I know how to convert a number in text format into number by copying a cell
having 1 as the entry and do pastespecial and apply "multiply". This trick
does not work on these numbers. Changing the format of these "numbers" in
number format also does not help. I wonder whether they are in currency
format(with <none> as the category;. I may not be able to send the
particular
file or window because there is no provision to save the window in any file
type(atleast I am not sure about this). perhaps the flle is not an excel
file and even not compatible to copy it in an excel spreadsheet. any ideas
how to go about this problem. I am sorry I am not able to explain more
clearly;;.
thanks and regards.

 

[Dave Peterson]

You said it isn't from a webpage, but maybe the output still has those HTML
nonbreaking spaces.

You could use David McRitchie's Trimall macro to try to clean it up.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

David also has instructions on how to install a macro and run it at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if that doesn't work, I'd try to find out what the problem characters are.

Chip Pearson has an addin that can show you what characters are there
exactly--not just what they look like (spaces and non-breaking spaces look the
same, but behave differently).

http://www.cpearson.com/excel/CellView.htm

Maybe you could use Chip's addin to determine the characters and then modify
David's macro to clean up your data.

 

[R.VENKATARAMAN]

Mr. Pattreson thank you for your suggestions. I have pearsons cell view
addin(hexchar.xla). I shall try to use this addin(I presume this is avilable
under view menu as <view cell contents> )
and also use Mr. mcritchie's macro. thanks once again. I shall revert to the
newgroup if I fail.

 

[R.VENKATARAMAN]

sorry for the delay in replying to this message. I should thank you for
suggesting the extremely useful link of Mr David McRitchie as well as Mr.
pearson's cellview add in. It took me time to understand the macro
"trimall" vis a vis my problem with my downloaded file and slighly modiify
the trimall macro to suit my purpose.. trimming the special cells after
removing chr(160) gave me some problem because one of the text values was
date in text that too in Indian style that is date/month. when I trim this
date it gate me a wrong date . e.g. when the date is given as 1/12 (that too
in text format with a spacae in the front) it means 1st december where as
after trimming the cell gave the value as january 12. that is why the need
for some modifications. This is the frist time I learnt about HTML non
breaking spaces.


Once again thank you for your wonrdreful guidance..