Ken,
The link did not appear in your post. Can I just do a web
search on Dave McRitchie and find his site?
Thanks for your assistance.
-----Original Message-----
CHAR(160) is a non breaking space character from Html.
Dave McRitchie is a Microsoft MVP who has
his own website full of free goodies (Examples,
spreadsheets and bits of code). TrimAll is a
macro that Dave wrote to deal specifically with this
kind
of problem (ie garbage characters in
your data). You need to go to his site with the link I
gave you, copy the code, paste it into a
module in your personal.xls file, and then select the
affected data and do Tools / Macro / Macros
/ Trimall.
This is only necessary if all the data has this
character
in it, but even then you could just use
the second formula I gave you.
As you have said all your data came in from a csv file then it is likely to be text.
MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP
-------------------------------------------------------
---
------------------
It's easier to beg forgiveness than ask permission
-------------------------------------------------------
---
------------------
What is CHAR(160) and Dave McRitchies Trimall macro?
I posted an additional reply with more info.
-----Original Message-----
I'm assuming you have copied your data straight from the
cell, and it appears that you have
CHAR(160) in there as the first space. If all the data
is like this then you can either clean it
up with Dave McRitchies Trimall macro:-
then use a formula such as the following in another
column to reference your data and bring it
back as a date:-
=DATEVALUE(LEFT(A2,FIND(" ",A2)-1)) - Then format as you
wish
or
With CHAR(160) still in your data you can simply amend
the formula to this:-
=DATEVALUE(LEFT(A1,FIND(CHAR(160),A1)-1))
Assuming your data is in Col A starting in A1, then put
either of the above formula sin B1 and
copy down. Then select all of Col B, copy and paste
special as values, and then delete Col A.
Format in the date format you wish.
--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP
----------------------------------------------------
---
--- ---
---
------------------
The date looks like this: 1990/9/25 12:00AM. I want
to
change the format to: 09/25/1990. When I try to change
the format...nothing happens.
Does it matter if the spreadsheet was developed
from
a
template. I have the same problem with the .xlt file.
-----Original Message-----
Sounds like your data is in text format - Post
back
and
give us some examples of how your data
looks and we can probably tell you how to fix it
to
do
what you want.
--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL2K
&
XLXP
-------------------------------------------------
---
--- ---
---
---
------------------
I do not have the ability to change the format
of
any
cells that have a date and time in them. How
can
I
accomplish this task? It seems that after I
edit
the
cell
and remove the AM or PM then I can reformat but I
don't
want to have to do this for every row.
.
.
.
