Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Reshaping a text in xls file

Subject: Reshaping a text in xls file

From: Jerry

Date: 23 Aug, 2012 17:10:08

Message: 1 of 12

Hi,

There is a 3x1 cell text within a xlsx file (see below):

[n, t] = xlsread('Test.xlsx')

n = []

t =
    'F|S|A|P|I|M|S|G|P|D|F'
    'K|E|F|T|F|A|P|T|S|S|F'
    'A|G|N|T|C|P|F'

I would like to convert it to this shape, which is 11x3 cell text:

t =

    'F' 'K' 'A'
    'S' 'E' 'G'
    'A' 'F' 'N'
    'P' 'T' 'T'
    'I' 'F' 'C'
    'M' 'A' 'P'
    'S' 'P' 'F'
    'G' 'T' ''
    'P' 'S' ''
    'D' 'S' ''
    'F' 'F' ''

Thanks,

Jerry

Subject: Reshaping a text in xls file

From: dpb

Date: 23 Aug, 2012 17:55:59

Message: 2 of 12

On 8/23/2012 12:10 PM, Jerry wrote:
...
> ... a 3x1 cell text ...
...
> t = 'F|S|A|P|I|M|S|G|P|D|F'
> 'K|E|F|T|F|A|P|T|S|S|F'
> 'A|G|N|T|C|P|F'
>
> I would like to convert it to this shape, which is 11x3 cell text:
>
> t =
> 'F' 'K' 'A'
> 'S' 'E' 'G'
...

Just takes some fiddling around...

 >> t=strrep(t,'|','');
 >> t(3)={[char(t(3)) blanks(4)]}
 >> c=char(t);
 >> reshape(cellstr(c(:)),3,11)'
ans =
     'F' 'K' 'A'
     'S' 'E' 'G'
     'A' 'F' 'N'
     'P' 'T' 'T'
     'I' 'F' 'C'
     'M' 'A' 'P'
     'S' 'P' 'F'
     'G' 'T' ''
     'P' 'S' ''
     'D' 'S' ''
     'F' 'F' ''
 >>

--

Subject: Reshaping a text in xls file

From: Bruno Luong

Date: 23 Aug, 2012 18:05:08

Message: 3 of 12

Just for fun, single line command

t = {'F|S|A|P|I|M|S|G|P|D|F';
     'K|E|F|T|F|A|P|T|S|S|F';
     'A|G|N|T|C|P|F'}

t = strrep(num2cell(char(strrep(t,'|',''))'),' ','')

% Bruno

Subject: Reshaping a text in xls file

From: Jerry

Date: 23 Aug, 2012 18:57:07

Message: 4 of 12

Thank you for your response, it worked. But, it seems that my data is more complex than what I supposed to be (see below)

[n, t] = xlsread('Test.xlsx','1')

n = []

t =

    'BM|TN|PL|FKI|TF|TG|AK|MP|MP|SL|JN|NG|K2|L6|FF2|F2|OD1'
    'CA|PSE|AB|JN|SK1|BR2|HE'
    'TNN|SMA|RXR|GA4|PX1'
    'NTCH|MD4|MAD|MAD2|GFB'

I would like to get this t

t =

    'BM|' 'CA|' 'TNN|' 'NTCH|'
    'TN|' 'PSE|' 'SMA|' 'MD4|'
    'PL|' 'AB|' 'RXR|' 'MAD|'
    'FKI|' 'JN|' 'GA4|' 'MAD2|'
    'TF|' 'SK1|' 'PX1' 'GFB'
    'TG|' 'BR2|' '' ''
    'AK|' 'HE' '' ''
    'MP|' '' '' ''
    'MP|' '' '' ''
    'SL|' '' '' ''
    'JN|' '' '' ''
    'NG|' '' '' ''
    'K2|' '' '' ''
    'L6|' '' '' ''
    'FF2|' '' '' ''
    'F2|' '' '' ''
    'OD1' '' '' ''

t=strrep(t,'|','');
let's keep this "|" since it allows me to distinguish each character from others

t(3)={[char(t(3)) blanks(4)]};
The data is relatively big and I couldn't go through the "t" to compute the size t1, t2..tn. Thus, I don't know how to modify this function to deal with my real data.

Thanks,

Jerry

dpb <none@non.net> wrote in message <k15qre$i90$1@speranza.aioe.org>...
> On 8/23/2012 12:10 PM, Jerry wrote:
> ...
> > ... a 3x1 cell text ...
> ...
> > t = 'F|S|A|P|I|M|S|G|P|D|F'
> > 'K|E|F|T|F|A|P|T|S|S|F'
> > 'A|G|N|T|C|P|F'
> >
> > I would like to convert it to this shape, which is 11x3 cell text:
> >
> > t =
> > 'F' 'K' 'A'
> > 'S' 'E' 'G'
> ...
>
> Just takes some fiddling around...
>
> >> t=strrep(t,'|','');
> >> t(3)={[char(t(3)) blanks(4)]}
> >> c=char(t);
> >> reshape(cellstr(c(:)),3,11)'
> ans =
> 'F' 'K' 'A'
> 'S' 'E' 'G'
> 'A' 'F' 'N'
> 'P' 'T' 'T'
> 'I' 'F' 'C'
> 'M' 'A' 'P'
> 'S' 'P' 'F'
> 'G' 'T' ''
> 'P' 'S' ''
> 'D' 'S' ''
> 'F' 'F' ''
> >>
>
> --

Subject: Reshaping a text in xls file

From: Bruno Luong

Date: 23 Aug, 2012 19:10:07

Message: 5 of 12

t = { 'BM|TN|PL|FKI|TF|TG|AK|MP|MP|SL|JN|NG|K2|L6|FF2|F2|OD1';
    'CA|PSE|AB|JN|SK1|BR2|HE';
    'TNN|SMA|RXR|GA4|PX1';
    'NTCH|MD4|MAD|MAD2|GFB'}

c = regexp(t,'\|','split');
n = length(c);
m = max(cellfun('length',c));
t = repmat({''},[m n]);
i = arrayfun(@(k) 1:length(c{k}),1:n,'unif',0);
j = arrayfun(@(k) k+zeros(1,length(c{k})),1:n,'unif',0);
t([i{:}]+m*([j{:}]-1)) = [c{:}]

% Bruno

Subject: Reshaping a text in xls file

From: Jerry

Date: 23 Aug, 2012 19:29:08

Message: 6 of 12

Thanks, it worked.
Jerry

"Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <k15v6f$pdn$1@newscl01ah.mathworks.com>...
> t = { 'BM|TN|PL|FKI|TF|TG|AK|MP|MP|SL|JN|NG|K2|L6|FF2|F2|OD1';
> 'CA|PSE|AB|JN|SK1|BR2|HE';
> 'TNN|SMA|RXR|GA4|PX1';
> 'NTCH|MD4|MAD|MAD2|GFB'}
>
> c = regexp(t,'\|','split');
> n = length(c);
> m = max(cellfun('length',c));
> t = repmat({''},[m n]);
> i = arrayfun(@(k) 1:length(c{k}),1:n,'unif',0);
> j = arrayfun(@(k) k+zeros(1,length(c{k})),1:n,'unif',0);
> t([i{:}]+m*([j{:}]-1)) = [c{:}]
>
> % Bruno

Subject: Reshaping a text in xls file

From: Jerry

Date: 23 Aug, 2012 20:00:08

Message: 7 of 12

How may I export "t" as an xls file?
Thanks,
Jerry

>
> "Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <k15v6f$pdn$1@newscl01ah.mathworks.com>...
> > t = { 'BM|TN|PL|FKI|TF|TG|AK|MP|MP|SL|JN|NG|K2|L6|FF2|F2|OD1';
> > 'CA|PSE|AB|JN|SK1|BR2|HE';
> > 'TNN|SMA|RXR|GA4|PX1';
> > 'NTCH|MD4|MAD|MAD2|GFB'}
> >
> > c = regexp(t,'\|','split');
> > n = length(c);
> > m = max(cellfun('length',c));
> > t = repmat({''},[m n]);
> > i = arrayfun(@(k) 1:length(c{k}),1:n,'unif',0);
> > j = arrayfun(@(k) k+zeros(1,length(c{k})),1:n,'unif',0);
> > t([i{:}]+m*([j{:}]-1)) = [c{:}]
> >
> > % Bruno

Subject: Reshaping a text in xls file

From: Bruno Luong

Date: 23 Aug, 2012 20:08:07

Message: 8 of 12

"Jerry " <jerrycholo@gmail.com> wrote in message <k16248$70a$1@newscl01ah.mathworks.com>...
> How may I export "t" as an xls file?

xlswrite?

Bruno

Subject: Reshaping a text in xls file

From: Jerry

Date: 23 Aug, 2012 20:13:07

Message: 9 of 12

I have already tried and I got this error:

xlswrite('mm.xls',t);
Error using xlswrite (line 220)
Excel returned: Error: Object returned error code:
0x800A03EC.

Jerry

"Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <k162j7$8l3$1@newscl01ah.mathworks.com>...
> "Jerry " <jerrycholo@gmail.com> wrote in message <k16248$70a$1@newscl01ah.mathworks.com>...
> > How may I export "t" as an xls file?
>
> xlswrite?
>
> Bruno

Subject: Reshaping a text in xls file

From: Bruno Luong

Date: 23 Aug, 2012 20:20:08

Message: 10 of 12

"Jerry " <jerrycholo@gmail.com> wrote in message <k162sj$9rp$1@newscl01ah.mathworks.com>...
> I have already tried and I got this error:
>
> xlswrite('mm.xls',t);
> Error using xlswrite (line 220)
> Excel returned: Error: Object returned error code:
> 0x800A03EC.
>

Perhaps your MATLAB or Excel is corrupted. It works for me.

Bruno

Subject: Reshaping a text in xls file

From: Bruno Luong

Date: 24 Aug, 2012 07:40:08

Message: 11 of 12

I think a for-loop is better solution after all:

c = regexp(t,'\|','split');
n = length(c);
m = max(cellfun('length',c));
t = repmat({''},[m n]);
for j=1:n
    cj = c{j};
    t(1:length(cj),j) = cj;
end

% Bruno

Subject: Reshaping a text in xls file

From: Jerry

Date: 24 Aug, 2012 12:58:07

Message: 12 of 12

"Bruno Luong" <b.luong@fogale.findmycountry> wrote in message <k17b4n$kj5$1@newscl01ah.mathworks.com>...
> I think a for-loop is better solution after all:
>
> c = regexp(t,'\|','split');
> n = length(c);
> m = max(cellfun('length',c));
> t = repmat({''},[m n]);
> for j=1:n
> cj = c{j};
> t(1:length(cj),j) = cj;
> end
>
> % Bruno

Hi,

Thank you! It seems that I need to modify this nice function that you wrote since the excel did not allow me to execute following things

If the "t" was a 4x2 cell (see below)

Class_1 CD|AB|PQ|AF|MAP1|MASS|CALA|
Class_2 AB|PQ|AF|MAP1|MASS|TXHU|MAP3K5|BCVA|
Class_3 CD|AB|PQ|AF|MAP1|
Class_4 NO|AB|GRE|MCAD|

I would like to convert it first to following structure:
Class_1 CD|
Class_1 AB|
Class_1 PQ|
Class_1 AF|
Class_1 MAP1|
Class_1 MASS|
Class_1 CALA|
Class_2 AB|
Class_2 PQ|
Class_2 AF|
Class_2 MAP1|
Class_2 MASS|
Class_2 TXHU|
Class_2 MAP3K5|
Class_2 BCVA|
Class_3 CD|
Class_3 AB|
Class_3 PQ|
Class_3 AF|
Class_3 MAP1|
Class_4 NO|
Class_4 AB|
Class_4 GRE|
Class_4 MCAD|

and then remove the duplicates within 2nd column using unique function (see below)
Class_1 CD|
Class_1 AB|
Class_1 PQ|
Class_1 AF|
Class_1 MAP1|
Class_1 MASS|
Class_1 CALA|
Class_2 TXHU|
Class_2 MAP3K5|
Class_2 BCVA|
Class_4 NO|
Class_4 GRE|
Class_4 MCAD|

Thanks again,
Jerry

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us