Data type cannot be converted in matlab or excel

Question

I recently copy and pasted some data from a database (USGS stream gauge data to be specific. I copy and pasted into excel, creating a column of my own for time.

When I import the data into matlab, only the column I made shows up.

obs = xlsread('ObservedMR.xlsx','jun30For');

I tried to convert the values in excel to numbers, but to no avail. In excel, the numbers are left justified (which I know means that they are not being registered as numbers), but there are no other characters visible.

When I create an empty matrix and try to copy and paste data in, I get an error reading that I cannot paste data that contains strings.

Using the following,

 p = readtable('ObservedMR.xlsx','Sheet','jun30For')

I get

p = 

x0       x0_31  
___    _________

  1    '0.31  ' 
  2    '0.31  ' 
  3    '0.31  ' 
  4    '0.31  ' 
  5    '0.31  ' 
  6    '0.31  '

I got error messages trying to use str2num (requires string or character input) and table2array.(types double and cell).

I was going to try to use

regexprep(p, ''' , '')

to replace the quotes, but I am getting messages about the single quotes being unclosed.

Does anyone know how I can use this data, by writing a code to edit out the quotes and spaces, import another way, convert it some way, etc?

Thank you!

you should be able to tell what format they are in when you select column right click and properties in excel. — GameOfThrows
It sounds like you have white space issues. In Excel try find and replace on the non-breaking space character. Otherwise, it could be that your regional settings expect a , as the decimal point but your sheet has .. Again, a find and replace in Excel is probably the easiest fix. — Dan
@GameOfThrows For some reason, excel doesn't allow me to view properties. I am using windows 10, so that could be why I cannot see it. — Laura Marlowe
@Dan I had tried the first option yesterday. I tried using the commas right now and that also did not work. — Laura Marlowe

Sembei Norimaki Sembei Norimaki · Accepted Answer · 2016-07-13T14:21:16

you can specify the format of the columns in readtable.

p = readtable('ObservedMR.xlsx','Sheet','jun30For', 'Format', '%f%f')

will read the columns as floats. If it doesn't do the conversion correctly, try reading them as strings %s and then using str2num once you have them in Matlab.

Anyway I would suggest correcting your data in Excel. If you click on the cell and look at the formula bar you will probably see a quote ' at the left of the number which indicates the cell is stored as text. Convert it to number, save the Excel and done.

Data type cannot be converted in matlab or excel

1 Answers