Find and Replace a specific String from excel work book

I have to find and replace a specific String from an excel work book which contains n number of sheets. The string is available in random location in different sheet's.Example I want to replace the string Rana_1 from all sheets with Akash_1 .One more thing format of the work book should not change. I tried regexp but it is not usefull .

Answer 1

[Rahul]

Hi @Akash Rana,

I understand that you wish to replace centrain cells with a particular text with a different text. To achieve this, consider using:

• This can be done by looping through each cell of the range of the excel sheet and checking as well as replacing the text in the cell with required text.
• Another approach can be to read the data from the excel file using 'readcell' function and the apply the tranformation to all the cells using 'cellfun' with a custom helper function to replace the text. Then 'writecell' can be used to write the modified data back to the excel sheet. Here is an example:

FileName = 'yourFile.xlsx';
oldString = 'Rana_1';
newString = 'Akash_1';

% Read, modify and write the data
data = readcell(FileName);
modifiedData = cellfun(@(x) replaceIfMatch(x, oldString, newString), data, 'UniformOutput', false);
writecell(modifiedData, FileName);

% Helper function to replace matching strings
function output = replaceIfMatch(input, oldStr, newStr)
    if ischar(input) && strcmp(input, oldStr)
        output = newStr;
    else
        output = input;
    end
end

Additionally, the following MATLAb Answer and File Exchange submission can be referred:

https://www.mathworks.com/matlabcentral/answers/873578-replace-cell-content-of-excel-from-matlab-script

https://www.mathworks.com/matlabcentral/fileexchange/42877-find-and-replace-in-files

The following MathWorks documentations can be referred to know more:

'readcell': https://www.mathworks.com/help/matlab/ref/readcell.html

'cellfun': https://www.mathworks.com/help/matlab/ref/cellfun.html

'writecell': https://www.mathworks.com/help/matlab/ref/writecell.html

Hope this helps! Thanks.