How to read an xls file in matlab which has latitude (suffixed with N), longitude (suffixed with E), time (prefixed with the month), and a value corresponding to latitude and longitude in it?

The xls file looks like this... The first column is the longitude. I want to extract only the number from it. For example, if the array has a value of "84.375E", I want "84.375" in my corresponding array's value and so on. For the column named "time", I want to similarly extract the number (year). For example, if the cell is "Jun-Sep 1990", I want my corresponding array to have a value of "1990".

I tried to load the values myself. I tried this code:

T = readtable('Myxlsx.xlsx',2);

But I got this error:

Error using readtable (line 245)
All parameters must have an associated value.

Then I tried the xlsread command like this:

T = xlsread('Myxlsx.xlsx');

But then only the values from the fourth column got loaded but nothing from the first three columns appeared.

[Anmol Dhiman]

Hi Abhishek,

Can you share your file for better debugging

[Abhishek Chakraborty]

Hi Anmol,

I have found the solution of this problem through another similar question I asked here. The link for the solution is :

https://in.mathworks.com/matlabcentral/answers/791464-how-to-convert-cell-array-containing-text-to-numbers?s_tid=srchtitle

Answer 1

[Monika Jaskolka]

You can use regexprep to clean up the values:

opts = detectImportOptions('Test.xlsx', 'NumHeaderLines', 2, 'VariableNamesRange', 'A1');
T = readtable('Myxlsx.xlsx', opts);	
T.Longitude = regexprep(T.Longitude, 'E$', ''); % Remove last E
T.Latitude = regexprep(T.Latitude, 'N$', ''); % Remove last N


T =

  4×4 table

    Longitude    Latitude           Time           Value  
    _________    _________    ________________    ________

    '84.375'     '8.57130'    'June-Sept 1990'    9.58e-06
    '86.25'      '8.57130'    'June-Sept 1990'    3.14e-05
    '88.125'     '8.57130'    'June-Sept 1990'    7.11e-05
    '90'         '8.57130'    'June-Sept 1990'    7.25e-05