Help Excel gurus..need to automatically reformat a text string

Here's the gist:

Column A cells contains HTML text block.
Within that block is a substring that needs to be reformatted and the results dumped into Column B

quote:

---

Example of text in column A
< html > blah blah ftp://userA:password@staging.domain.com/path/image1.jpg" blah blah < / html >

---

I want to grab *JUST* the FTP portion (in bold) and return the results in a string in column B that looks like this:

quote:

---

http://production.domain.com/userA

---

The tricky thing is I need to pick out that "userA" (this is actually the same name as the filepath) and add it to the end of the HTTP path

Make sense?

The final step (THIS WOULD HAPPEN LATER) would be to swap the BOLD portion of the example in the first quote with the second quote but that will be done via DB query.

The list in Column B will form the input for the query. The result will ultimately look like this:

quote:

---

http://production.domain.com/userA/path/image1.jpg

---

I have 10,000 rows to fix, each one has a different "userA" that needs to be pulled out and reinserted at the end.

Anyone good with string substitutions? TIA