Cleansing URLs

Hi Smartsheet Community,

I'm trying to streamline URLs that are being submitted to my team.

=IF(
    ISNUMBER(FIND("https://www.", [Original url]@row)), SUBSTITUTE([Original url]@row, "https://www.", "")
      , IF(ISNUMBER(FIND("https://", [Original url]@row)), SUBSTITUTE([Original url]@row, "https://", "")
      , IF(ISNUMBER(FIND("http://www.", [Original url]@row)), SUBSTITUTE([Original url]@row, "http://www.", "")
      , IF(ISNUMBER(FIND("http://", [Original url]@row)), SUBSTITUTE([Original url]@row, "http://", "")
      , IF(ISNUMBER(FIND("www.", [Original url]@row)), SUBSTITUTE([Original url]@row, "www.", "")
      , [Original url]@row
    ))))
  )

I wrote this formula which should derive the whole URL to just the domain and page path. However, it's only working for URLs starting with "https://www.". Is this because Smartsheets is recognizing the results as hyperlinks, so my formula isn't working (screenshot below - left is a form value, right column is the formula)?

Does anyone know a fix?

Thank you!

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @jlazo

    Here you go. Let me know if you need any adjustments you can't get working. Example sheet:

    Formula:

    =IF(CONTAINS("https://www.", [Website URL]@row), REPLACE([Website URL]@row, 1, 12, ""), IF(CONTAINS("https://", [Website URL]@row), REPLACE([Website URL]@row, 1, 8, ""), IF(CONTAINS("http://www.", [Website URL]@row), REPLACE([Website URL]@row, 1, 11, ""), IF(CONTAINS("http://", [Website URL]@row), REPLACE([Website URL]@row, 1, 7, ""), [Website URL]@row))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!