export file from txt (Notepad)

[Guest]

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

 

[Sharad Naik]

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

 

[Guest]

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