How to remove the ' charachter

[Darin Kramer]

Howdie,

I have the date that has been imported, and it is displayed as '20
January 2006

Now I know that the ' defines it as text

Question is how to a make it a number(ie a date format) ...? Find and
Replace ' doesnt work, nor does formatting cells....???

Thanks

D

Thanks

 

[nickysquawkes]

If you use the following functions to extract the day month and year:

=day(cell ref)
=month(cellref)
=year(cellref)

then use the date function selecting the above cell (E.G
date(daycellref,monthcellref,yearcellref).

That should convert it to a serial number.

Nic

 

[JMay]

In VBA:

Activecell.Value = Activecell.Value

HTH

 

[Guest]

With date in A1:

Range("b1") = CDate(Range("a1"))

 

[Guest]

In the worksheet (not VBA) use the =DATEVALUE() function. It does a good job
of converting text into a real date (and doesn't care about leading
apostrophes)

 

[Darin Kramer]

Using day, month or year I get a #value error...?

 

[Darin Kramer]

That changed one of my funny dates to a number, but no others.... any
idea why?

 

[JMay]

Smart Dates in excel are numbers;
Now Format the number as Date;

 

[Darin Kramer]

Thanks, my question better phrased is why when I applied the VBA to 100
funny dates, it only changed 1 of those funny dates to a number...?

 

[Guest]

If it presented literally a number and not a date as most people view a date,
then it would be because the cell format was not set to some format that
would display a date. A specific example would be that the cell format was a
number and not a date. I don't think that the cell format must be a date
format, but it is an example where a number entered (i.e. 36784) would appear
as a date (9/15/2000). If you got the reverse, that only one cell showed up
as a date, and the rest remained as text like 20 January 2005, then that's a
separate issue to which I currently have no answer... Sorry.

 

[Guest]

My example, using activecell - only performs the conversion on the active
cell, thus your-one-cell...
If you highlight the range of cells you wish to Convert and use
from the immediate window:
Selection.value = Selection.value (press enter key)

All highlighted cells will be converted to numbers, where you can format as
Dates
if excel doesn't do it for you..

HTH

 

[Darin Kramer]

Thanks - Do I just put that line of text into VB..? not sure how to
activate it once I have selected range of text...?

 

[Darin Kramer]

I got it to work, but for some reason it happily works on a date like
2-2-2006 (correctly converts it to 2 Feb 2006,) BUT for dates lik
19-2-2006 it doesnt give a result. I THINK that it thinks the month is
19, so gets confused... ? possible? cause it happens consistently...

 

[Guest]

Correct, it is thinking that 19 is the month which is based on the regional
options of your computer. If you can test for cases like that, you can
"rearrange" the data to something sensical... Given the two examples you
provided here, it would appear that the data is stored calendar day month
then year... Therefore of course the 2nd day of the 2nd month works out
right.

May need to swap the two items at the beginning. Maybe not the best way:
New text = mid of the text & left of everything before the first dash &
everything after the first dash, of course with dashes still in there. If you
use the ampersand, ensure that you have a space before it and after it. Once
you get that new string, you can actually produce your handy dandy date. The
other option is just to change your regional settings so that it recognizes
dates as day first month second. But it will need to be that way on every
computer that the program will be run.

Not a good programming "style".