When Vidyasagar standardised the language based on his own dialect, which had lost most of the distinction, he just ditched the now-booted letter altogether (whereas the sister language Assamese retains a letter for it). Bengali used to have a letter for w like basically all other Indic languages, but the written character merged with ব ba. The /w/ use is where things get a little bit stupid. If one were to make a rule out of this, the rule would probably be 'if preceded or succeeded by a close/high vowel, then the letter য় is pronounced /j/'. The sound can be a semivowel version of /i/, /j/, as well, especially when the regular phoneme is impractical, a good example being the word bāyu 'wind'. The natural sound it makes is generally what you have cited from Wikipedia, a semivowel version of /e/, /e̯/, for example, মায়া māyā.

The letter was created essentially because Bengalis no longer pronounced the Sanskrit letter য ya the same at the beginnings of words, so we made it so that the old letter ya be pronounced ja ( antastha ja) and made a new letter which modifies the old one with a bindu/ nuqta (়) to represent something near the original sound, which on paper is only to be used when non-word-initial. অন্তস্থ য় antastha ya (many people say antastha a instead) is a bit of a tricky letter which we can thank Vidyasagar for.

English letter to bengali letter