How to paste nicely into Excel from MATLAB variable editor?

286 visualizaciones (últimos 30 días)
Julian
Julian el 25 de Feb. de 2015
Comentada: Will Reeves el 27 de Sept. de 2023
One can readily paste data from Excel into a MATLAB cell array in the variable editor using Paste Excel Data (Ctrl-Shift-V), but how exactly does one reverse this operation?
I find cells that contain [] become strings in Excel, where I expect blank cells, and strings get unwanted quotes at the start and end.
The net result of this is if you copy and paste some cells in Excel comprising some text, some numeric and some blanks into a MATLAB cell array - it looks fine in MATLAB - but when you copy back to Excel you don't have what you started out with.
Easy copy / paste between Excel and MATLAB should be a breeze in 2015, shouldn't it? What am I doing wrong? Perhaps there is or needs to be a "Copy Excel Data" function to match "Paste Excel Data" (although it is nicer to just use standard Ctrl+C, Ctrl+V)?
Thanks for reading this.

Respuesta aceptada

Julian
Julian el 9 de Mzo. de 2015
It is not directly possible. From Mathworks:
I understand that you would like to paste cells with strings from the MATLAB Variables editor to Microsoft Excel, but noted some inconsistencies to-from copy/paste operations. Specifically, you found that copying from MATLAB to Excel in this way introduces single quotation marks and empty square brackets depending on whether or not the cell element has a string or is an empty matrix.
Unfortunately, while the MATLAB Variables editor has a context menu item to paste values from Excel, I do not think there is an immediate option in Microsoft Excel to paste from MATLAB. I tried using Excel's Text Import Wizard to perform this paste operation into Excel, but unfortunately none of the options I explored worked.
That said, I can think of two possible workarounds to working between MATLAB and Excel without the use of "xlsread/xlswrite" or "readtable/writetable": 1. Copy the cell array from the MATLAB Variables editor, and then paste to Excel as you have done before. Then, from Excel, press Ctrl+F to Find/Replace all single quotes and square brackets [] with empty strings. 2. Consider using MathWork's Spreadsheet Link EX to use MATLAB from Microsoft Excel. The following link provides a high-level overview of Spreadsheet Link EX and its available features: http://www.mathworks.com/products/excellink/
  1 comentario
Will Reeves
Will Reeves el 27 de Sept. de 2023
Please can there be a checkbox in the viewer to enable the hiding of speach marks? it would be really helpful. Or perhaps a "copy as" similar to the "paste excel data" (which initself feels like a bit of a hack?)

Iniciar sesión para comentar.

Más respuestas (9)

P D McClanahan
P D McClanahan el 16 de Nov. de 2017
One easy workaround is to: 1) Paste into Google sheets, which seems to work fine 2) Then copy from Google sheets into Excel

Lex
Lex el 8 de Mayo de 2021
This answer is very late given when the question was asked, but I found success just directly copy and pasting (Ctrl+C and Ctrl+V) , then going to "Data" and selecting "Text to Columns" and then following the instructions.
You would have to play with it to figure out what works for you, but there are instructions online how to use "Text to Columns."

Jg
Jg el 21 de En. de 2016
Julian,
I'm no Matlab guru so I like simple solutions like you're looking for.
I've had luck with Ctrl+C to copy from the variable editor, then paste into Microsoft Word. Then copy and paste from word to Excel.
May work for you too.
  2 comentarios
Julian
Julian el 21 de En. de 2016
Thanks for the tip Jg.
Sadly it didn't work just now in my current (outdated) environment, but I shall try again later today when I have an up-to-date version of everything (Windows, Excel & MATLAB!)

Iniciar sesión para comentar.


Zhe
Zhe el 5 de Jul. de 2017
Copy and paste to Google Sheets then to Excel

Daniel
Daniel el 25 de Feb. de 2015
I don't know much about converting back Excel, but you might want to look into xlswrite. That command may do the trick for you.
  2 comentarios
Julian
Julian el 25 de Feb. de 2015
Thanks for answering, Daniel, but not what I was looking for. I know about xlswrite, but that is a programmatic solution and (I think) doesn't work for spread-sheets already open in Excel. I just want to know how to Ctrl+C Ctrl+V into an open Excel, which should be very simple surely?
Will Reeves
Will Reeves el 27 de Sept. de 2023
clearly not... And still an issue in 2023

Iniciar sesión para comentar.


Sascha Schlechtweg
Sascha Schlechtweg el 15 de Mayo de 2017
Using Ctrl+C and Ctrl + V in OpenOffice worked perfectly fine for me. Maybe it helps.
Best regards

Julian
Julian el 15 de Mayo de 2017
Thanks for your point. I don't have OpenOffice but I confirm that behaviour I first complained about still applies in MATLAB R2017a.

Joel Bay
Joel Bay el 4 de Mzo. de 2020
None of these other methods seem to work anymore, but what you can do is:
writetable(cell2table(myCellArray));
And then you'll have a nicely copy and pasteable table in your work directory.
  1 comentario
Julian
Julian el 4 de Mzo. de 2020
Thanks for your comment!
Yes, I certainly use writetable and readtable - but that solution is not copy & paste and creates intermediary files....

Iniciar sesión para comentar.


Rafid Bendimerad
Rafid Bendimerad el 31 de Mzo. de 2020
Editada: Rafid Bendimerad el 31 de Mzo. de 2020
Here are the steps:
1- Copy your array from Matlab.
2- Paste your array in Excel.
3- Select your array in Excel.
4- type: Ctrl + F (Press the button Ctrl and the button F simultaneously).
5- A window will appear (Find - Replace). Choose Replace.
6- Then,
Find what: .
Replace with: ,
(Look to the picture below)
It's Done.
Basically, we just replace the period (.) by a coma (,). And this is bacause Matlab uses "periods" while Excel uses "comas". For example: Matlab writes: 5.5 while Excel writes 5,5.
Got it ?
Good Luck.
  1 comentario
Steven Lord
Steven Lord el 31 de Mzo. de 2020
Different countries use different decimal separators. I know Microsoft Excel has an option to specify the decimal and thousands separators. MATLAB has a preference to control what is used when exporting data via the clipboard.

Iniciar sesión para comentar.

Etiquetas

Productos

Community Treasure Hunt

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

Start Hunting!

Translated by