Substituting data in cell to clean data

[Vinod Aithal]

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 times each lesson has been taught. However, our app has a feature where users can modify our lessons, which changes its name from 'Reading lesson' to 'Copy of Reading lesson', or sometimes they choose their own names like 'Bob's Reading lesson'.

To clean this data, and make sure all variations of each lesson are counted correctly, I would like to search within the column of lessons to find all cells containing the text 'Reading lesson', and then replace all the different variations with 'Reading lesson'. Then I need to repeat this for our other 303 lessons. I'm sure there's a way, but my colleagues and I can't work it out! Can anyone make any suggestions?

Thanks in advance

Vinod

[#2]

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)))

[#3]

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 times

[#4]

Hi 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+A

display 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?