You are now following this question
- You will see updates in your followed content feed.
- You may receive emails, depending on your communication preferences.
Use matlab to search and replace in an excel sheet
8 views (last 30 days)
Show older comments
Hello,
I load an excel file with matlab R2015b using "xlsread". I would like to use matlab to search through the excel sheet for points and replace it with commas. Any ideas how do do that?
Thanks
22 Comments
Stephen23
on 2 Nov 2018
Is it required to use MATLAB? Why not just VBA, or find-and-replace inside Excel?
mael thevenot
on 2 Nov 2018
Currently I'm opening each excel file, click on find&replace, replace dot to commas then save.
Then I'm using a mat file to work on the data in the excel sheet.
But I will have to do that for thousand of files, so I'm searching a way to automatise this ^^"
Caglar
on 2 Nov 2018
Edited: Caglar
on 2 Nov 2018
Both excel and Matlab has a setting for the decimal separator. Both of them can be set to work with comma or dot. Are you sure you cannot solve your problem that way?
If you really want to change dots to commas, you can load the file with xlsread, use strrep function with cellfun to change whatever you want and then save it back to excel with xlswrite. You may need to convert numerics from Excel to strings for this to work properly.
Note that, even in R2018a (I dont know about R2018b) xlswrite cannot write strings. You will need to convert back to chars or numerics after you make your string operation.
mael thevenot
on 5 Nov 2018
MMh how could I do to tell matlab to use the dot as a decimal separator when I import excel files? I searched it and did not find any way to do it.
madhan ravi
on 5 Nov 2018
Stephen23
on 5 Nov 2018
"how could I do to tell matlab to use the dot as a decimal separator when I import excel files? I searched it and did not find any way to do it."
MATLAB only recognizes the decimal point (i.e. period/dot) as the decimal separator. It does not recognize any other character (i.e. decimal commas are not recognized). This means there is no setting that you can/need to change.
mael thevenot
on 5 Nov 2018
Matlab use the decimal point for everything I do, but for my xlsread command it do not work with dots.. If I use xlsread and have dots in my file, I obtain nonsense values. If I replace all dots to commas in my excel, I obtain my matrix of data with xlsread, with all commas being replaced by dots automotically by matlab.
mael thevenot
on 5 Nov 2018
Ok, and I use
xlsread(filename, 'Feuil1', range);
range being all the data except the 1st column.
Guillaume
on 5 Nov 2018
Edited: Guillaume
on 5 Nov 2018
@Calgar,
"Both excel and Matlab has a setting for the decimal separator. Both of them can be set to work with comma or dot"
As far as I know this is not true for either. Excel uses Windows regional settings, it doesn't have its own. Matlab has very little support for , as a decimal separator. The only place it is aware of it is when exporting to the clipboard. It does not support it for import.
"xlswrite cannot write strings"
xlswrite can write strings just fine.
@mael thevenot,
As I said above, Excel uses Windows regional settings. So you can always change your regional settings to use a . instead of , as a decimal separator. Note that it will affect other programs. On my machine (Win10 + Excel 365), as soon as I change the decimal symbol in the regional settings, Excel automatically reflects the change.
However, it is really not clear what problem you are having. If the number are really stored as numbers in Excel, then whichever separator Excel uses does not matter for matlab. Matlab, using xlsread or readtable (or even through actxserver) will read the numbers with a . separator. That's because matlab doesn't read the textual representation of the numbers but their binary representation.
Of course, if the numbers are stored as text, then matlab will read the text as it is. But then they're not numbers in excel as well.
So, the whole "search through the excel sheet for points and replace it with commas" seems pointless. If it's for matlab, it's a non-issue and if it's for excel, you can't do that without changing the numbers to text and preventing you use them in calculation.
Walter Roberson
on 5 Nov 2018
It is an xlsx file. The numbers are probably stored as text, because xlsx is a compressed folder of XML files.
mael thevenot
on 5 Nov 2018
Well the problem I'm having is xlsread does not work with my excel files if I keep the dot as decimal separator in it. Cells do not have a particular format (standard format in my excel2016 version).
If I manually replace dots to commas, xlsread work and in Matlab I have my data, with dot as decimal separator. I've attached one of my excel file above, idk if you will have the same result than I do?
@Walter I tried changing the format of cells to numbers : with commas in my excel it work fine as well, but if I have dots I obtain a NaN matrix
Guillaume
on 5 Nov 2018
"It is an xlsx file. The numbers are probably stored as text, because xlsx is a compressed folder of XML files".
Yes, the numbers are stored as text in the xlsx file (with a dot as decimal separator by the way!). However, that doesn't matter, since xlsread does not read the file itself (unless in basic mode). Instead it talks to Excel through COM and ask for the Value property of the range which is going to return values of type VARIANT|VT_R8 (double) most likely, certainly not text.
Guillaume
on 5 Nov 2018
@mael,
Which OS are you using?
I have changed my regional settings so that , is the decimal separator. Opened your essai3.xlsx in excel and checked that it is indeed using ,. Restarted matlab just to be sure then
v = xlsread('essai3.xlsx')
shows all numbers with a . in matlab.
I'm using a newer version of matlab than yours but that shouldn't matter since xlsread was using the same mechanism in your version as it do now to communicate with excel.
mael thevenot
on 5 Nov 2018
@Guillaume,
I'm using windows 10 professionnel. And if you keep your regional setting that way but replace all commas to dots in the excel file, you should face the same probleme than me with the xlsread command, don't you?
Guillaume
on 5 Nov 2018
What does replace all commas to dot in the excel file actually mean?
If the numbers are stored as numbers in excel, the only way to replace all commas to dot is by changing the regional settings.
As I said, with comma regional settings and the file showing commas in excel, xlsread still return the correct numbers in matlab.
Stephen23
on 5 Nov 2018
Edited: Stephen23
on 5 Nov 2018
"And if you keep your regional setting that way but replace all commas to dots in the excel file, you should face the same probleme than me with the xlsread command, don't you?"
Excel shows values according to the locale settings, but they are always saved in xlsx files with periods. Changing the data in the worksheet without changing the locale (or options) just change the data from numeric to text, or vice versa. Not very useful at all.
I work for a large company where many use decimal commas and many use decimal points, and yet all Excel documents open correctly according my locale/options. I have never needed to change commas/periods like this.
What are you actually trying to do? What is the actual problem?
Walter Roberson
on 5 Nov 2018
I opened the file in Office for Mac, and I read the file using xlsread() on Mac (which does not talk to Excel to read it), and I read the file using readtable(). All of the values appear to be the same; a numeric comparison of the first 100 entries in column 2 is bitwise identical between xlsread() and readtable()
mael thevenot
on 7 Nov 2018
@Stephen my regional settings are already using commas by default. My problem is that I have .tdms files that I save in xlsx format, that are using a dot separator (I just receive those files like that and save them as xlsx). When I use xlsread I have a matrix of NaN, but if I search&replace commas to dots, xlsread give me a correct matrix...
@Walter readtable seems to work fine but I can't manage to delimitate which range of cells I want with this command :/
I just changed my regional settings to dot for decimal separator, start matlab, tried xlsread with an excel file with dot : NaN matrix. And I got correct values if I search&replace dots to commas......
Stephen23
on 7 Nov 2018
"My problem is that I have .tdms files that I save in xlsx format, that are using a dot separator (I just receive those files like that and save them as xlsx)."
It sounds like that conversion is not working correctly. How do you "save in xlsx format"? With what tool? Please upload a sample .tdms file for us.
Guillaume
on 7 Nov 2018
I assume the tdms files are converted to xlsx using National Instrument excel add-in (that is automatically installed with LabView). Note that there is a tdms reader in the file exchange which allows you to read the tdms file directly into matlab.
A sample of a generated xlsx file would be useful.
"start matlab, tried xlsread with an excel file with dot : NaN matrix" Then it doesn't look like your problem has anything to do with dot vs comma.
mael thevenot
on 8 Nov 2018
I said something wrong sorry, the xlsx that are not working are from another soft, where there is a button "copy data" and then I can copy those data in an excel (and it have a dot as decimal separator). The default format of the cell is "standard" in my excel 2016. I tried to change the format of cells (text, number, ...) but xlsread do not work until I search&replace dots to commas.
If there is no way to do this change in matlab, I'll just go on by doing it manually, thanks for your time.
Answers (0)
See Also
Categories
Find more on Data Import from MATLAB in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!An Error Occurred
Unable to complete the action because of changes made to the page. Reload the page to see its updated state.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)