The following imported data isn't recognized as a dates:

Jul 4 2008 6:30AM
Jun 22 2007 5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
........................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first.
using "=left" function can provide the month, but the placement of the year
varies by 1 character in the middle of the cell, so "=mid" can't be utilized.

I have used "=trim" function and tried the "=Datevalue" function also.

Any suggestions?

It appears that you have the web symbol CHAR(160) in your text. To extract
just the month and year, you can do this:

=LEFT(A2,3)&" "&MID(A2,FIND(CHAR(160),A2,5)-4,4)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"slf" wrote:

The following imported data isn't recognized as a dates:

Jul 4 2008 6:30AM
Jun 22 2007 5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
.......................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first.
using "=left" function can provide the month, but the placement of the year
varies by 1 character in the middle of the cell, so "=mid" can't be utilized.

I have used "=trim" function and tried the "=Datevalue" function also.

Any suggestions?

On jan. 4, 21:41, slf wrote:
The following imported data isn't recognized as a dates:

Jul *4 2008 *6:30AM
Jun 22 2007 *5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
.......................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first. *
using "=left" function can provide the month, but the placement of the year
varies by 1 character in the middle of the cell, so "=mid" can't be utilized.

I have used "=trim" function and tried the "=Datevalue" function also..

Any suggestions?

Try this:

=DATEVALUE(SUBSTITUTE(LEFT(TRIM(A1),SEARCH("/",SUBSTITUTE(TRIM(A1),"
","/",3)))," ","/"))

Not tested, because US Regional settings and English language Excel is
needed to properly evaluate this formula, I have a national language
version, but it should work.

Regards,
Stefi

Try this:
Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Luke M" wrote:

It appears that you have the web symbol CHAR(160) in your text. To extract
just the month and year, you can do this:

=LEFT(A2,3)&" "&MID(A2,FIND(CHAR(160),A2,5)-4,4)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"slf" wrote:

The following imported data isn't recognized as a dates:

Jul 4 2008 6:30AM
Jun 22 2007 5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
.......................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first.
using "=left" function can provide the month, but the placement of the year
varies by 1 character in the middle of the cell, so "=mid" can't be utilized.

I have used "=trim" function and tried the "=Datevalue" function also.

Any suggestions?

This little macro might work (or not, date math is very vulnerable to
regional settings). Select the cells in question and run this:


Sub test()
Dim Cel As Range
On Error Resume Next
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Value = DateValue(Cel.Value) + TimeValue(Cel.Value)
Next
End Sub

HTH. Best wishes Harald

"slf" wrote in message
...
The following imported data isn't recognized as a dates:

Jul 4 2008 6:30AM
Jun 22 2007 5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
.......................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first.
using "=left" function can provide the month, but the placement of the
year
varies by 1 character in the middle of the cell, so "=mid" can't be
utilized.

I have used "=trim" function and tried the "=Datevalue" function also.

Any suggestions?

On Jan 4, 10:11*pm, Stefi wrote:
On jan. 4, 21:41, slf wrote:





The following imported data isn't recognized as a dates:

Jul *4 2008 *6:30AM
Jun 22 2007 *5:59PM

I have tried both of these formulas found elsewhere in the Excel Community
without luck:

=DATE(RIGHT(A1,4),LEFT(A1,LEN(A1)-6),MID(A1,LEN(A1)-5,2))

=TEXT(TEXT(A1,"00000000"),"00-00-0000")+0
.......................
I need the month and year only, text to columns work but requires the data
to be copied elsewhere first. *
using "=left" function can provide the month, but the placement of the year
varies by 1 character in the middle of the cell, so "=mid" can't be utilized.

I have used "=trim" function and tried the "=Datevalue" function also.

Any suggestions?

Try this:

=DATEVALUE(SUBSTITUTE(LEFT(TRIM(A1),SEARCH("/",SUBSTITUTE(TRIM(A1),"
","/",3)))," ","/"))

Not tested, because US Regional settings and English language Excel is
needed to properly evaluate this formula, I have a national language
version, but it should work.

Regards,
Stefi- Hide quoted text -

- Show quoted text -

This is tested:
=DATEVALUE(MID(A2,4,3)&"/"&LEFT(A2,3)&"/"&RIGHT(LEFT(TRIM($A$2),SEARCH
("/",SUBSTITUTE(TRIM($A$2)," ","/",3))-1),4))
It requires English Regional settings and English language Excel
version.

Regards,
Stefi