Create subroutine VBA to replace keyword in a column

[william516]

It has become too time consuming to simply use the find and replace method as there are probably 100+ combinations of words that need to be found and shortened or changed when information has been copied and pasted into the worksheet page. Here is an example of what I am looking to do.

Run a subroutine once all the information has been manually imported / copied and pasted from a word document into the excel sheet "Initiating Devices"
Column (B) Is the type column. When the information is originally pasted the words are too long or need to be changed to make the countif statements on another page to work, and to fit in the cell.

Example
Smoke Detector would be replaced with "Photo"
Pull Station "Pull"
Heat Detector "Heat"
etc.......
The list could go on for a while. I have been manually using the find and replace method but it has been a slow process and not only that but I sometimes need to search for several words to replace the same word.
for example
Manual Station, Pull Station, Pull Box, Emergency Station, ...etc is all replaced with "PULL"
The problem was that originally there was no set format and the inspectors simply wrote whatever they wanted. Now I am restricting it to a drop down list, but I still need to convert all the old data into the new sheets.

So the sub would look for a word
"Smoke Detector" in column B and replace it with "Photo" for all occurrences it would find on the page. If possible could it also search for "Photo Detector", "PS,PD or other combination. I tried using the record macro method but it doesn't work the way it should and usually results in an error when run. Thanks. If someone can just get me started I can write the tons of other replacements needed later. Thanks

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA