Performing find and replace functions on elements of a table in Matlab

Question

I am working with a 400x1200 imported table (readtable generated from an .xls) which contains strings, doubles, dates, and NaNs. Each column is typed consistently. I am looking for a way to locate all instances in the table of any given string ('Help me please') and replace them all with a double (1). Doing this in Matlab will save me loads of work making changes to the approach used on the rest of this project.

Unfortunately, all of the options I've looked at (regexp, strrep, etc) can only take a string as a replacement. Strfind was similarly unhelpful, because of the typing across the table. The lack of cellfun has also made this harder than it should be. I know the solution should have something to do with finding the indices of the strings I want and then just looping DataFile{subscript} = [1], but I can't find a way to do it.

Answer 1

What you can do is as follows:

[rows, cols] = size(table); % Get the size of your table
YourString = 'Help me please'; % Create your string
Strmat = repmat(YourString,rows,cols); % Stretch to fill a matrix of table size
TrueString = double(strcmp(table,Strmat)); % Compares all entries with one another

TrueString now contains logicals, 1 where the string 'Help me please' is located, and 0 where it is not.

If you have a table containing multiple classes it might be handy to switch to cells though.

Answer 2

First you should transform your table at a cell array.

Then, you can use the strrep along with str2num, e.g.

% For a given cell index
strrep(yourCellIndexVariable, "Help me please", "1");
str2num(yourCellIndexVariable);

This will replace the string "Help me please" with the string "1" (the strrep function) and the str2num will change the cell index to the double value according to the string.

By yourCellIndexVariable I mean an element from the cell array. There are several ways to get all cells from a cell array, but I think that you have solved that part already.

Answer 3

Thank you very much everyone for helping think through to a solution. Here's what I ended up with:

% Reads data
[~, ~, raw] = xlsread ( 'MyTable.xlsx');
MyTable = raw;

% Makes a backup of the data in table form
MyTableBackup = readtable( 'MyTable.xlsx' );

% Begin by ditching 1st row with variable names
MyTable(1,:) = [];

% wizard magic - find all cells with strings
StringIndex = cellfun('isclass', MyTable, 'char');

% strrep goes here to recode bad strings. For example:
MyTable(StringIndex) = strrep(MyTable(StringIndex), 'PlzHelpMe', '1');

% Eventually, we are done, so convert back to table
MyTable = cell2table(MyTable);

% Uses backup Table to add variable names
% (the readtable above means the bad characters in variable names are already escaped!) 
MyTable.Properties.VariableNames = MyTableBackup.Properties.VariableNames;

This means the new values exist as strings ('1', not 1 as a double), so now I just str2double when I access them for analysis. My takeaway - Matlab is for numbers. Thanks again all!