Is it possible to have a column data with some numerical values and some strings?

I have some really large data with millions of rows. I would have a column with 95% of them being numerical and 5% being strings. For example:

A = [53; 6; 77; 26b; 47; d33; 2; 5; c4; 77; 6];

As you know, in Matlab, A would be stored as a string column. Here is the problem, when I export them into Excel, you would see an error (yellow arrow) saying numer stored as a text. I would have to go through the entire column to click "convert to number", so as to remove these errors.

So how do I store the column data A so that some of them will be numerical and some of them will be strings. Here is what I did:

B = str2double(A);           % strings will be converted to NaN
Ind = ~isnan(B);             % only identify rows that are not NaNs
A(Ind) = str2double(A(Ind)); % only convert strings that can be converted to numerical values to numerical.

Theoretical it will work. Unfortunately, Matlab will store all vallues of A as strings again.

Anyone knows how to solve this issue?

[Fangjun Jiang]

Did you try cell array? A={53; 6; 77; '26b'; 47; 'd33'; 2; 5; 'c4'; 77; 6}

[Leon]

Thanks, but I can not process this manually, as I have millions of rows.

Liqing

Answer 1

[Fangjun Jiang]

Where does A come from? In MATLAB, A can not be stored as A = [53; 26b] or A={53; 26b}.

If the source data comes from Excel file, you can use [Num,Txt,Raw]=xlsread() to read and then process to get a cell array of numerical and string data.

[Leon]

They came from multiple Excel files, I decided to read them all as if they are string.

The probem occurs when I try to export the standardized vallues back to a new Excel file.

[Fangjun Jiang]

Construct a cell array from numerical and string data and then export

A=[1;2;3];
b={'b';'c'};
A=num2cell(A);
A=vertcat(A,b);

[Leon]

Many thanks.