Forum Discussion

Alina1185's avatar
Alina1185
Copper Contributor
Aug 16, 2022

Find and Replace Function

Hi,

 

I have to prepare my excel sheet for further calcualations and therefore have to replace a huge number of exponents (e.g. 4,95E-05) with a filler (NaN). As the exponents are all different, I am searching for a way in which I don't have to replace every single value manually but to replace them all at once.

 

Thanks!

excel
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 16, 2022

    If those are in a single column you could filter the column for "E-" and then just select all visible cells then type NaN and hit control+enter.

     

    Altenatively, just click control+F, Find E-, click Find All. Then in the found items list you can click the first one, shift+click the last one and then hit Escape. Now all cells containing E- are selected. Next, to be on the safe side, hit F5, click Special, click Constants (if the cells must not contain a formula) and uncheck all boxes EXCEPT numbers. Click OK. Only cells with numbers should now be selected. Type NaN and hit control+Enter. Done.

Resources