Separate Date and Time from excel file column
조회 수: 1 (최근 30일)
이전 댓글 표시
DD22122015000001
I have time column in excel as
DD22122015000101
DD22122015000201
DD22122015000301
etc
they are 1 day file 22/12/2015 at 00:01:01, 00:02:01 , 00:03:01 etc and corresponding parameters
I wish to separate this column of time into two columns dd/mm/yyyy and 00:01:01 ...etc
any help will be highly helpful.
댓글 수: 0
채택된 답변
Azzi Abdelmalek
2016년 1월 15일
v={'DD22122015000101';'DD22122015000201';'DD22122015000301'}
d=cellfun(@(x) [x(3:4) '/' x(5:6) '/' x(7:10)],v,'un',0)
t=cellfun(@(x) [x(11:12) ':' x(13:14) ':' x(15:16)],v,'un',0)
추가 답변 (1개)
Walter Roberson
2016년 1월 15일
편집: Walter Roberson
2016년 1월 15일
To get the values as datenums:
date_column = 5; %for example
[num, txt] = xlsread('TheFile.xls');
datecell = txt(:,date_column);
dates = datenum(regexprep(datecell, '^DD', ''), 'ddmmyyyyHHMMSS');
The regexprep() is there to throw away the 'DD' from the beginning of the string.
You can compute with the dates or you can datestr() to get text.
If you do not care about the numeric representation of the dates and just want to break it up into two columns, then
date_column = 5; %for example
[num, txt] = xlsread('TheFile.xls');
datecell = txt(:,date_column);
datesfmt = regexprep(datecell, '^\s*(?:DD)(\d\d)(\d\d)(\d\d\d\d)(\d\d)(\d\d)(\d\d)\s*', '$1/$2/$3 $4:$5:$6');
dates = regexp(datesfmt, ' ', 'split');
dates = vertcat(dates{:});
datesfmt would have the elements reformatted but single string per line, not broken into two columns. The line after that splits each line into the two columns, but the result is an N x 1 cell each of which is a 1 x 2 cell. The last of the lines recreates it as an N x 2 cell, so dates(:,1) is the date column and dates(:,2) is the time column.
댓글 수: 0
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!