Find and Replace Function

Occasional Visitor



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.



1 Reply

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.