This is right about offloading the CPU as far as playing games with 3D sound effects and, in case of some cards, DVD playback goes.
MP3 playback all is pretty much done by CPU (decoding the stream)
For most of the people the real issue should be the quality of the analog output from the card. The difference can be heard on even cheap headphones and better speakers and it matters more when listening to music. What makes this difference is the quality of the codec or DAC (a chip converting digital data to analog signal) used on the card and the design of the card itself (to minimize noise, interference from other parts of computer etc)
The onboard AC97 sound is usually using cheap, lower quality codecs/DAC’s and is close to other components so the noise is higher.
For example my ancient SoundBlaster Live! sounds better than onboard sound from my 1 year old motherboard (Realtek AC97 codec)
I think Analog Devices AC97 codecs are better.
If you use digital output from the card then the analog conversion quality is determined by the quality of the amplifier (usually pretty good)
There are differences on the digital side too (like for example most of the Creative sound cards converting audio to 48kHz sampling rate for playback) The sampling rate for MP3 and CD is 44.1kHz. Every conversion results in some loss of quality.