They certainly don’t get anywhere near as much recognition as the A4s, Deltics or HSTs, but arguably they’re as significant as the A4s and more significant to the ECML than both the Deltics and the HSTs. The 225s ran the majority of core long distance services on the ECML for 30 years with (once the initial teething troubles were resolved) relatively little fuss and some are still going strong now. As the flagship fleet on the ECML they are the longest serving - others had more years in service on the ECML but none were the flagship fleet for anywhere near as long as the 225s were.
For total longevity in ECML long distance service the 225s are currently third, behind the A3s and the HSTs. If the 225s make it to 2027 in service as was recently speculated elsewhere they'll have matched the A3s. They'd need to make it to 2030 to match the HST, I don't think that'll happen but never say never!
Looking at 225 operations today, they might not be the flagship fleet on the ECML anymore but they remain popular with both staff and passengers. Sometimes you even see 'normals' stop to take photos of the 91s at the buffer stops at Leeds P6, especially with 91110 and 111 but to some extent with all of them.
There’s a very good legacy left by the 225s. Even if nothing like them has been built for the UK since, they proved that 225kmh loco hauled sets were viable. Though not officially cited as such, the 225s influenced the design of the Austrian Railjets which are themselves successful record breakers like the 225s, and the Railjets have gone on to achieve some of what the 225s were intended to achieve but didn't, such as locomotives coming off high speed coaching sets in the evening and running freight overnight.
To answer the question in the thread title, whilst the 225s might not have achieved everything BR set out for them to achieve they have been a success. They've achieved some things beyond what was ever envisaged for them by BR too - I doubt BR ever expected them to run to Stirling or Skipton!