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?

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?

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

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

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?