Cleansing URLs

jlazo

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!

Mike TV

@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))))

Mike TV

@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))))

jlazo

I was trying way too hard. Thank you for the simple solution 🙏