Forum Discussion
Substituting data in cell to clean data
Hi, I'm trying to clean the data in a column of a spreadsheet and I can't work out the best way to do it. I have a list of lessons used by users of our education app, and I need to know how many tim...
Hi Vinod,
To replace variation you may select your column, when Ctrl+H and replace like this
Alternatively you may count without replacing using formula like
=SUMPRODUCT(--ISNUMBER(SEARCH("Reading lesson",$A$1:$A$500)))- Thanks Sergei,
That's almost what I'm looking for.
I should have said that I'm also using Office 2016 for Mac. When I use double quote marks around the search string, it isn't able to find any results.
I did consider find and replace, but the only problem is that I would have to repeat this over 300 times in order to clean the data. Is there a way to save myself some work and do the find/replace across two ranges of cells?
The sumproduct is handy, but after the lesson titles are cleaned, I need to segment them by time/teacher/territory etc. so it's a bit early to be counting them. It'll also be something I'll need to do 300 timesHi Vinod,
I'm on Windows, can say nothing about how it is on Mac.
As for Find and Replace I didn't catch why you shall repeat that few hundred times. You may select your range or even entire sheet
+Adisplay the Replace dialog box (I guess the same Ctrl+H), add texts and after that click Replace all. All texts will be replaced at once. Or that doesn't work in your case?
