Here’s a simple, one-step solution for PowerRename that will work for all your files, keeping the 4-digit file number, sheet number, and revision, and removing everything else in between:

Enable Use Regular Expressions, then use this search:

^(.*?)(\d{4}).*?SHEET-(\d+).*?Rev\.?\.?([A-Za-z0-9]+)

and this replace:

MOD-CR1A-DDT-$2-$3_$4

This will transform filenames like:

MOD-CR1A-DDT-0001-CONCRETE-----SHEET-2-OF-3-CONCRETE-FOUNDATION-PLANS-Rev.4

into:

MOD-CR1A-DDT-0001-2_4