Problem Importing Merged Text From Excel to Matlab

Hello!

I wonder if you have experienced such a problem. I am having difficulty importing the text cells in Matlb with the following function:

[Num,Txt,Raw]=xlsread('test.xls');

The text cells are the results of the merging of the various cells that can be like these

D  1  DT  D1DT
D  2  DC  D2DC
D  3  UT  D3UT
D  4  UC  D4UC
D  5  DT  D5DT
D  6  DC  D6DC

the last column is the concatenated result of merging the first three columns....but they are treated as NaN values.....Interesting to note that when I manually types something in excel cell it is treated as text by Matlab...I am not sure why it does not treat merged or concatenated text as the same... I hope this can be overcome somehow..)

I will be happy to hear any opinion!)

Thanks!

D

Answer 1

[Fangjun Jiang]

In the Excel sheet, the fourth column contains a formula like "=CONCATENATE(A1,B1,C1)". That must be the reason it was treated as nan by xlsread(). There are ways to deal with it, but that is probably what it is for xlsread(). I didn't research to get to the bottom of it.

I could suggest two solutions.

1. Pre-process the Excel file, you can copy the whole sheet and then paste special, select values.

2. Do the concatenation in MATLAB. If Data is the raw data read in by xlsread, then do this:

Data(:,4)=[Data(:,1),Data(:,2),Data(:,3)];

[Dima]

yes thanks...copying and pasting as values overcomes this but I was thinking this can be done automatically....I will try concatenating in Matlab and let you know...thanks so much for your continued assistance!)

[Dima]

ONE FAST QUESTION?

MATLAB TREATS ALL TEXTUAL RESULT OF ANY FORMULA IN EXCEL AS NAN?

[Fangjun Jiang]

It seems like that way but I couldn't find a definite answer. You could open xlsread.m to see if there is any clue. Or, make an Excel file with the formula and put a break point in xlsread.m to debug and find out. Or, ask any Mathworker to provide an insight?

[Walter Roberson]

I don't have excel to experiment with (and cannot reach my server today anyhow.) I'm wondering whether it would still be treated as NaN if the text "looked like" a number? For example, if you were to =CONCATENATE(B1,B2,B3) then the result should look like 123 -- would that be parsed as a number or as NaN ? Or suppose the result of the concatenation was 'inf' (infinity) -- how would that be interpreted?

I would then try again with numeric formula to see whether the key is that it is a formula, or the key is that it is text, or the key is that it is text that is not interpretable as a number.

[Fangjun Jiang]

Maybe it's even related to MATLAB version, MS Office Version and OS version. I just tried on R2007b, Office 2003 and Windows XP.

>> [Num,Txt,Raw]=xlsread('test.xls')

Num =

1 2 3 6

Txt =

'A' 'B' 'C' 'ABC'

Raw =

'A' 'B' 'C' 'ABC'

[1] [2] [3] [ 6]

In the Excel file, D1=CONCATENATE(B1,B2,B3) D2=SUM(A2:C2).

It looks like xlsread() reads the value.

Answer 2

[Dima]

I am still having problem IMPORTING THE CONCATENATED text...I wonder if anyone has encountered such a problem before? thanks!

[Jared Lou]

I'm having a similar problem. I found another thread where someone resorted to readtable. It sounds like some suspect it's related to a specific combo of matlab version, excel version & OS?