The Source engine's distance variance feature requires sounds to be formatted in a specific way, so you can’t just drop in any custom sound and expect it to work. Distance-based sounds store both the near and far sounds in the same file using stereo channels: the left channel is for the near version of the sound, and the right channel is for the far version.

So adapting a custom sound is actually pretty straightforward - split stereo into mono, apply the effects to the right channel for the far sound and combine them back into a stereo track.

As for what effects to apply to right channel, here's my presets[files.catbox.moe] and a little video[files.catbox.moe] of what I did in Audacity, which is:

1. Two EQ passes
   
2. Bass and Tremble – around -6.0 bass
   
3. Compressor – "Great Body SFX", "Great Impact SFX" or my preset
   
4. Limiter – around -3.0 threhold

Of course, I encourage you to experiment with the effects yourself, as my method certainly isn't perfect since I'm not exactly an audio engineer.