Got Questions? Get Answers.
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:
Reading Excel xlsread cell with formula in them

Subject: Reading Excel xlsread cell with formula in them

From: Dez

Date: 29 Mar, 2012 15:16:18

Message: 1 of 10

Hi,

Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )

I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.

My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.

I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.

I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.

Thanks for any help.

Subject: Reading Excel xlsread cell with formula in them

From: John Smith

Date: 29 Mar, 2012 20:41:30

Message: 2 of 10

"Dez" wrote in message <jl1uc2$de9$1@newscl01ah.mathworks.com>...
> Hi,
>
> Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )
>
> I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.
>
> My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.
>
> I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.
>
> I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.
>
> Thanks for any help.

What version of matlab and excel are you using? Because my matlab (2007b) can import formulas fine from excel file formats 97-2003, 2007 etc.
Have you tried saving your Excel file as a CSV file and importing it like that?

Subject: Reading Excel xlsread cell with formula in them

From: TideMan

Date: 29 Mar, 2012 19:10:06

Message: 3 of 10

On Friday, March 30, 2012 4:16:18 AM UTC+13, Dez wrote:
> Hi,
>
> Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )
>
> I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.
>
> My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.
>
> I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.
>
> I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.
>
> Thanks for any help.

Well, that's odd.
Using my version of Matlab (2006a) and Excel 2002, this works fine. The cell with the formula in it returns that numerical value.

Subject: Reading Excel xlsread cell with formula in them

From: YUC hu

Date: 16 Apr, 2012 03:29:08

Message: 4 of 10

I have similar problem lately. Where the cell formula is Bloomberg's BDP function
Simply copy value of the sheet containing BDP function to another worksheet and read with XLSREAD, everything works fine.



"Dez" wrote in message <jl1uc2$de9$1@newscl01ah.mathworks.com>...
> Hi,
>
> Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )
>
> I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.
>
> My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.
>
> I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.
>
> I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.
>
> Thanks for any help.

Subject: Reading Excel xlsread cell with formula in them

From: Dez

Date: 16 Apr, 2012 08:08:06

Message: 5 of 10

"YUC hu" <k9999@56.com> wrote in message <jmg3m4$d9p$1@newscl01ah.mathworks.com>...
> I have similar problem lately. Where the cell formula is Bloomberg's BDP function
> Simply copy value of the sheet containing BDP function to another worksheet and read with XLSREAD, everything works fine.
>
>
>
> "Dez" wrote in message <jl1uc2$de9$1@newscl01ah.mathworks.com>...
> > Hi,
> >
> > Slightly strange problem i need to read a value off of an Excel spread sheet which has a formula in it ( A bloomberg one to be precise )
> >
> > I can import spread sheet data no problem into matlab using the function xlsread but if there is any formula in the cell it returns NaN.
> >
> > My question is there anyway to import the actual value of the cell formula into matlab so it just reads it as a number.
> >
> > I.e =C1+C2 in the cell and if c1 and c2 are both 5 it would import 10.
> >
> > I do realise i would be better actually importing pure data and recreating the function in matlab but this isn't possible in this particular case.
> >
> > Thanks for any help.

Thanks for the response! I am no longer getting an error kick back when i try to read the cell it just reads as empty.

subsetA =

     []

Like that. I was thinking is it because i am calculating a live figure on excel? It is constantly updating so possibly it can't read it into matlab.

I basically want matlab to read the figure from the spread sheet and then e-mail it to an external server so it can keep me updated while i am travelling.

Subject: Reading Excel xlsread cell with formula in them

From: tolsthoorn@gmail.com

Date: 17 Jun, 2013 07:24:34

Message: 6 of 10

Whenever I read excel data info Matlab, and a cell contains a formula that yields a text value, I get a NaN in Matlab. This happens even if I change the format of the excel sheet cell to text. This way I can never read dynamic texts from Excel, which largely reduces the value of xlsread.

For instance, a cell in Excel contains a formula yielding a text value that I want to read into Matlab to use a header of a figure. The text in Excel adapts automatically when I change the data to reflect some alternative situation.

It happens when using xlsread on Mac OS X. Mac OS X is handicapped enormously form the fact that xlsread on the Mac can only read old 95/5.0 Excel file versions. As long as the Mathworks refuses to update this for its Mac users, we have to live with it. But it is mighty irritating. I hope someone has solved this issue.

Theo Olsthoorn, the Netherlands

Subject: Reading Excel xlsread cell with formula in them

From: Steven_Lord

Date: 17 Jun, 2013 14:37:58

Message: 7 of 10



<tolsthoorn@gmail.com> wrote in message
news:20a162b8-1c1a-4d40-8aeb-efc8ade3dce6@googlegroups.com...
> Whenever I read excel data info Matlab, and a cell contains a formula that
> yields a text value, I get a NaN in Matlab. This happens even if I change
> the format of the excel sheet cell to text. This way I can never read
> dynamic texts from Excel, which largely reduces the value of xlsread.

In order to evaluate that formula, you need to be running Microsoft Excel.
Correct?

As an analogy, I can write the formula:

sin(0)

inside a MATLAB program file. If I tried to read that program file in C++,
all I'll get is the text string "sin(0)". In order to obtain the _value_ of
that expression, I need to evaluate it in MATLAB.

> For instance, a cell in Excel contains a formula yielding a text value
> that I want to read into Matlab to use a header of a figure. The text in
> Excel adapts automatically when I change the data to reflect some
> alternative situation.

The text may change, but only when the file is being processed by Excel and
the formula is evaluated.

> It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> enormously form the fact that xlsread on the Mac can only read old 95/5.0
> Excel file versions. As long as the Mathworks refuses to update this for
> its Mac users, we have to live with it. But it is mighty irritating. I
> hope someone has solved this issue.

As of release R2012b, according to the Release Notes for MATLAB:

"The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
platforms. Previously, this functionality was available only on Microsoft
Windows systems with Excel software."

http://www.mathworks.com/help/matlab/release-notes.html

I don't think that will resolve the problem you're experiencing, though,
which is that you want MATLAB to be able to evaluate Excel "code" as part of
the reading process. On Windows, if you have Microsoft Excel installed,
MATLAB uses COM to interact with Excel and assuming that the file is set to
automatically reevaluate that formula when the file is opened, you would be
able to do what you want. But I'm not certain that approach is possible on
Macs.

--
Steve Lord
slord@mathworks.com
To contact Technical Support use the Contact Us link on
http://www.mathworks.com

Subject: Reading Excel xlsread cell with formula in them

From: Marc

Date: 18 Jun, 2013 02:17:09

Message: 8 of 10

"Steven_Lord" <slord@mathworks.com> wrote in message <kpn707$e2u$1@newscl01ah.mathworks.com>...
>
>
> <tolsthoorn@gmail.com> wrote in message
> news:20a162b8-1c1a-4d40-8aeb-efc8ade3dce6@googlegroups.com...
> > Whenever I read excel data info Matlab, and a cell contains a formula that
> > yields a text value, I get a NaN in Matlab. This happens even if I change
> > the format of the excel sheet cell to text. This way I can never read
> > dynamic texts from Excel, which largely reduces the value of xlsread.
>
> In order to evaluate that formula, you need to be running Microsoft Excel.
> Correct?
>
> As an analogy, I can write the formula:
>
> sin(0)
>
> inside a MATLAB program file. If I tried to read that program file in C++,
> all I'll get is the text string "sin(0)". In order to obtain the _value_ of
> that expression, I need to evaluate it in MATLAB.
>
> > For instance, a cell in Excel contains a formula yielding a text value
> > that I want to read into Matlab to use a header of a figure. The text in
> > Excel adapts automatically when I change the data to reflect some
> > alternative situation.
>
> The text may change, but only when the file is being processed by Excel and
> the formula is evaluated.
>
> > It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> > enormously form the fact that xlsread on the Mac can only read old 95/5.0
> > Excel file versions. As long as the Mathworks refuses to update this for
> > its Mac users, we have to live with it. But it is mighty irritating. I
> > hope someone has solved this issue.
>
> As of release R2012b, according to the Release Notes for MATLAB:
>
> "The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
> platforms. Previously, this functionality was available only on Microsoft
> Windows systems with Excel software."
>
> http://www.mathworks.com/help/matlab/release-notes.html
>
> I don't think that will resolve the problem you're experiencing, though,
> which is that you want MATLAB to be able to evaluate Excel "code" as part of
> the reading process. On Windows, if you have Microsoft Excel installed,
> MATLAB uses COM to interact with Excel and assuming that the file is set to
> automatically reevaluate that formula when the file is opened, you would be
> able to do what you want. But I'm not certain that approach is possible on
> Macs.
>
> --
> Steve Lord
> slord@mathworks.com
> To contact Technical Support use the Contact Us link on
> http://www.mathworks.com

I apologize if my comment is treating you like a newbie but are you using the following format with xlsread()....

[YourData, YourText] = xlsread();

Any cell with text will give an NaN in YourData and will be shown in YourText.

This works fine for me with Mac OS 10.8.3, Matlab 2012b and Office 2011 for Mac with .xlsx and .xlsm files. By default, I am saving everything as .xlsm, which seems to give me the most consistent results on my Macs. Here column B has the values and column C simply is "=B2...."

tic
[YourData, YourText] = xlsread('TextVsNum.xlsm','Sheet1', 'B2:C7');
toc

Elapsed time is 0.042532 seconds.
>> YourText

YourText =

    'Your data' 'Your data'
    'My data' 'My data'
    '' ''
    '' ''
    'five' 'five'

>> YourData

YourData =

     2 2
     3 3
   NaN NaN
     5 5

Subject: Reading Excel xlsread cell with formula in them

From: Marc

Date: 18 Jun, 2013 02:28:16

Message: 9 of 10

"Marc" wrote in message <kpofv5$qoj$1@newscl01ah.mathworks.com>...
> "Steven_Lord" <slord@mathworks.com> wrote in message <kpn707$e2u$1@newscl01ah.mathworks.com>...
> >
> >
> > <tolsthoorn@gmail.com> wrote in message
> > news:20a162b8-1c1a-4d40-8aeb-efc8ade3dce6@googlegroups.com...
> > > Whenever I read excel data info Matlab, and a cell contains a formula that
> > > yields a text value, I get a NaN in Matlab. This happens even if I change
> > > the format of the excel sheet cell to text. This way I can never read
> > > dynamic texts from Excel, which largely reduces the value of xlsread.
> >
> > In order to evaluate that formula, you need to be running Microsoft Excel.
> > Correct?
> >
> > As an analogy, I can write the formula:
> >
> > sin(0)
> >
> > inside a MATLAB program file. If I tried to read that program file in C++,
> > all I'll get is the text string "sin(0)". In order to obtain the _value_ of
> > that expression, I need to evaluate it in MATLAB.
> >
> > > For instance, a cell in Excel contains a formula yielding a text value
> > > that I want to read into Matlab to use a header of a figure. The text in
> > > Excel adapts automatically when I change the data to reflect some
> > > alternative situation.
> >
> > The text may change, but only when the file is being processed by Excel and
> > the formula is evaluated.
> >
> > > It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> > > enormously form the fact that xlsread on the Mac can only read old 95/5.0
> > > Excel file versions. As long as the Mathworks refuses to update this for
> > > its Mac users, we have to live with it. But it is mighty irritating. I
> > > hope someone has solved this issue.
> >
> > As of release R2012b, according to the Release Notes for MATLAB:
> >
> > "The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
> > platforms. Previously, this functionality was available only on Microsoft
> > Windows systems with Excel software."
> >
> > http://www.mathworks.com/help/matlab/release-notes.html
> >
> > I don't think that will resolve the problem you're experiencing, though,
> > which is that you want MATLAB to be able to evaluate Excel "code" as part of
> > the reading process. On Windows, if you have Microsoft Excel installed,
> > MATLAB uses COM to interact with Excel and assuming that the file is set to
> > automatically reevaluate that formula when the file is opened, you would be
> > able to do what you want. But I'm not certain that approach is possible on
> > Macs.
> >
> > --
> > Steve Lord
> > slord@mathworks.com
> > To contact Technical Support use the Contact Us link on
> > http://www.mathworks.com
>
> I apologize if my comment is treating you like a newbie but are you using the following format with xlsread()....
>
> [YourData, YourText] = xlsread();
>
> Any cell with text will give an NaN in YourData and will be shown in YourText.
>
> This works fine for me with Mac OS 10.8.3, Matlab 2012b and Office 2011 for Mac with .xlsx and .xlsm files. By default, I am saving everything as .xlsm, which seems to give me the most consistent results on my Macs. Here column B has the values and column C simply is "=B2...."
>
> tic
> [YourData, YourText] = xlsread('TextVsNum.xlsm','Sheet1', 'B2:C7');
> toc
>
> Elapsed time is 0.042532 seconds.
> >> YourText
>
> YourText =
>
> 'Your data' 'Your data'
> 'My data' 'My data'
> '' ''
> '' ''
> 'five' 'five'
>
> >> YourData
>
> YourData =
>
> 2 2
> 3 3
> NaN NaN
> 5 5

I should have been clear w.r.t saving and consistency with .xlsm.... A lot of my excel files from work contain application based macros to get stuff from databases, historians and analytical equipment, so saving everything as .xlsm has worked best for me across both platforms.

Subject: Reading Excel xlsread cell with formula in them

From: Marc

Date: 23 Jul, 2013 05:03:10

Message: 10 of 10

"Marc" wrote in message <kpogk0$s1j$1@newscl01ah.mathworks.com>...
> "Marc" wrote in message <kpofv5$qoj$1@newscl01ah.mathworks.com>...
> > "Steven_Lord" <slord@mathworks.com> wrote in message <kpn707$e2u$1@newscl01ah.mathworks.com>...
> > >
> > >
> > > <tolsthoorn@gmail.com> wrote in message
> > > news:20a162b8-1c1a-4d40-8aeb-efc8ade3dce6@googlegroups.com...
> > > > Whenever I read excel data info Matlab, and a cell contains a formula that
> > > > yields a text value, I get a NaN in Matlab. This happens even if I change
> > > > the format of the excel sheet cell to text. This way I can never read
> > > > dynamic texts from Excel, which largely reduces the value of xlsread.
> > >
> > > In order to evaluate that formula, you need to be running Microsoft Excel.
> > > Correct?
> > >
> > > As an analogy, I can write the formula:
> > >
> > > sin(0)
> > >
> > > inside a MATLAB program file. If I tried to read that program file in C++,
> > > all I'll get is the text string "sin(0)". In order to obtain the _value_ of
> > > that expression, I need to evaluate it in MATLAB.
> > >
> > > > For instance, a cell in Excel contains a formula yielding a text value
> > > > that I want to read into Matlab to use a header of a figure. The text in
> > > > Excel adapts automatically when I change the data to reflect some
> > > > alternative situation.
> > >
> > > The text may change, but only when the file is being processed by Excel and
> > > the formula is evaluated.
> > >
> > > > It happens when using xlsread on Mac OS X. Mac OS X is handicapped
> > > > enormously form the fact that xlsread on the Mac can only read old 95/5.0
> > > > Excel file versions. As long as the Mathworks refuses to update this for
> > > > its Mac users, we have to live with it. But it is mighty irritating. I
> > > > hope someone has solved this issue.
> > >
> > > As of release R2012b, according to the Release Notes for MATLAB:
> > >
> > > "The xlsread function now reads data from XLSM, XLTX, and XLTM files on all
> > > platforms. Previously, this functionality was available only on Microsoft
> > > Windows systems with Excel software."
> > >
> > > http://www.mathworks.com/help/matlab/release-notes.html
> > >
> > > I don't think that will resolve the problem you're experiencing, though,
> > > which is that you want MATLAB to be able to evaluate Excel "code" as part of
> > > the reading process. On Windows, if you have Microsoft Excel installed,
> > > MATLAB uses COM to interact with Excel and assuming that the file is set to
> > > automatically reevaluate that formula when the file is opened, you would be
> > > able to do what you want. But I'm not certain that approach is possible on
> > > Macs.
> > >
> > > --
> > > Steve Lord
> > > slord@mathworks.com
> > > To contact Technical Support use the Contact Us link on
> > > http://www.mathworks.com
> >
> > I apologize if my comment is treating you like a newbie but are you using the following format with xlsread()....
> >
> > [YourData, YourText] = xlsread();
> >
> > Any cell with text will give an NaN in YourData and will be shown in YourText.
> >
> > This works fine for me with Mac OS 10.8.3, Matlab 2012b and Office 2011 for Mac with .xlsx and .xlsm files. By default, I am saving everything as .xlsm, which seems to give me the most consistent results on my Macs. Here column B has the values and column C simply is "=B2...."
> >
> > tic
> > [YourData, YourText] = xlsread('TextVsNum.xlsm','Sheet1', 'B2:C7');
> > toc
> >
> > Elapsed time is 0.042532 seconds.
> > >> YourText
> >
> > YourText =
> >
> > 'Your data' 'Your data'
> > 'My data' 'My data'
> > '' ''
> > '' ''
> > 'five' 'five'
> >
> > >> YourData
> >
> > YourData =
> >
> > 2 2
> > 3 3
> > NaN NaN
> > 5 5
>
> I should have been clear w.r.t saving and consistency with .xlsm.... A lot of my excel files from work contain application based macros to get stuff from databases, historians and analytical equipment, so saving everything as .xlsm has worked best for me across both platforms.

Another minor point, brought up in several other threads, with 2012b and on, xlsread started shaving off leading and lagging data and text. In the above example, rows 2 through 7 should give "vectors" with 6 rows, but as you can see I ended up with 5 and 4 rows.

This has been a real pain and I am trying to find a link to the "answers" post were you can comment a line out of xlsread to make this fungible with previous matlab versions.

NOT THAT I SUPPORT THAT.... but everyone here are adults and can make there own choices.

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