Use the free GarageBand app on your iPhone to turn your sound file into a ringtone.

Once converted, go to Settings > Sounds & Haptics > Text Tone to assign the new sound to messages.

Common text message sounds for videos or apps. Screenshot Sound: Specific camera capture sounds.

If you want to use these downloaded sounds as custom text tones or ringtones, you can do so for free: Download: Save your chosen sound file to your Files app.

You can also download specialized, free apps like Text Message Sounds from the App Store.