Problem Importing Merged Text From Excel to Matlab

6 次查看（过去 30 天）
Dima
Dima 2011-9-23
评论： Jared Lou 2019-1-2
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

请先登录，再回答此问题。

回答（2 个）

Fangjun Jiang
Fangjun Jiang 2011-9-23
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)];
  5 个评论
显示 3更早的评论
Walter Roberson
Walter Roberson 2011-9-24
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
Fangjun Jiang 2011-9-24
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.

请先登录，再进行评论。

Dima
Dima 2012-1-23
I am still having problem IMPORTING THE CONCATENATED text...I wonder if anyone has encountered such a problem before? thanks!
  1 个评论
Jared Lou
Jared Lou 2019-1-2
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?

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by