export file from txt (Notepad)

Hi to all

I have open exported file of notepad (TXT Format) Where i have data like
date, item,amtin,amtout,bal.
where data format is DD-MM-YYYY (05-01-2005)
the problem is when i conver this data from text to column, date comes in
number format like 20050105.

is there any way i can put seprater(-) after 4 character and than after 2
charcter (like 2005-01-05 )
any help

Thanks

Shital shah

Hello Shital

Assign the Date read from the txt file to a variable.
In below example I assume that you assigned it to variable name "xyz". You
can replace xyz with your variable.

'You already assigned it to variable xyz
Dim myDate As Date
myDate = DateSerial(Left(xyz, 4), Mid(xyz, 5, 2), Right(xyz, 2))

'Now myDate is correct date
'you can assign it to a cell value uisng = myDate
'it will show as say 5/1/2005 if the cell format is Date "d/M/yyy"

'Or if you must have it in "year-month-day" format you can use:
Dim strDate As String
strDate = Left(xyz, 4) & "-" & Mid(xyz, 5, 2) & "-" & Right(xyz, 2)

'And assign it to cell value using = strDate

Sharad

"shital shah" <[email protected]> wrote in message
news:[email protected]...
> Hi to all
>
> I have open exported file of notepad (TXT Format) Where i have data like
> date, item,amtin,amtout,bal.
> where data format is DD-MM-YYYY (05-01-2005)
> the problem is when i conver this data from text to column, date comes in
> number format like 20050105.
>
> is there any way i can put seprater(-) after 4 character and than after 2
> charcter (like 2005-01-05 )
> any help
>
> Thanks
>
> Shital shah
>

Hello Mr. Sharad Naik

you have replyed my question on dt. 8th Jan 05 but still can't find a
solution i don't have more knowladge of macro so where to put lines

Dim myDate As Date
myDate = DateSerial(Left(xyz, 4), Mid(xyz, 5, 2), Right(xyz, 2))

and how to work if you can help me it's a grate help to me.
and soory to reply you in your direct E-mail ID.

regards

shital

"Sharad Naik" wrote:

> Hello Shital
>
> Assign the Date read from the txt file to a variable.
> In below example I assume that you assigned it to variable name "xyz". You
> can replace xyz with your variable.
>
> 'You already assigned it to variable xyz
> Dim myDate As Date
> myDate = DateSerial(Left(xyz, 4), Mid(xyz, 5, 2), Right(xyz, 2))
>
> 'Now myDate is correct date
> 'you can assign it to a cell value uisng = myDate
> 'it will show as say 5/1/2005 if the cell format is Date "d/M/yyy"
>
> 'Or if you must have it in "year-month-day" format you can use:
> Dim strDate As String
> strDate = Left(xyz, 4) & "-" & Mid(xyz, 5, 2) & "-" & Right(xyz, 2)
>
> 'And assign it to cell value using = strDate
>
> Sharad
>
> "shital shah" <[email protected]> wrote in message
> news:[email protected]...
> > Hi to all
> >
> > I have open exported file of notepad (TXT Format) Where i have data like
> > date, item,amtin,amtout,bal.
> > where data format is DD-MM-YYYY (05-01-2005)
> > the problem is when i conver this data from text to column, date comes in
> > number format like 20050105.
> >
> > is there any way i can put seprater(-) after 4 character and than after 2
> > charcter (like 2005-01-05 )
> > any help
> >
> > Thanks
> >
> > Shital shah
> >
>
>
>