Prev: How to insert lines into a workbook without changing references?
Next: use Find without hardcoded value but a value from a cell
From: slf on 4 Jan 2010 15:41
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?
From: Luke M on 4 Jan 2010 15:59
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?
From: Stefi on 4 Jan 2010 16:11
On jan. 4, 21:41, slf <s...(a)discussions.microsoft.com> 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
From: ryguy7272 on 4 Jan 2010 16:28
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?
From: Harald Staff on 4 Jan 2010 17:18
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" <slf(a)discussions.microsoft.com> wrote in message
news:3F73C619-214C-4682-9D2F-9AFE0B8E4235(a)microsoft.com...
> 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?

 |  Next  |  Last
Pages: 1 2
Prev: How to insert lines into a workbook without changing references?
Next: use Find without hardcoded value but a value from a cell