Extracting part of a cell to return in another cell

Devon Eddy
Devon Eddy ✭
inFormulas and Functions

I'm trying to come up with a formula which will take the website URL in one cell, extract what's called the root domain (website.com) without www. or https:// or any url string after the .com

Here's a couple of examples of the value I have in the cell and what I would like it to return in a horizontal cell:

http://www.internationalrealestatedirectory.com/country/united_states/builders.htm->internationalrealestatedirectory.com

https://home-directory.biz->home-directory.biz

http://www.regionaldirectory.us/->regionaldirectory.us


Is this something that would be possible given, that there are multiple domain extensions and multiple scenarios that could be before the beginning of the domain? If so, help with the formula would be MUCH appreciated.

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi@Devon Eddy

    Try something like this.


    Start with adding three so-called helper columns.

    You can call them,Position,Contains, andNew

    Add the following formulas(the URL column is the one that includes you're starting URL)


    Position

    =FIND("/"; Contains@row)


    Contains

    =IF(CONTAINS("http://www.";URL@row); SUBSTITUTE(URL@row; "http://www.";""); IF(CONTAINS("https://";URL@row); SUBSTITUTE(URL@row; "https://";"")))


    New

    =LEFT(Contains@row; Find@row - 1)


    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.


    This URLhttp://www.internationalrealestatedirectory.com/country/united_states/builders.htmwould show as internationalrealestatedirectory.com after the conversion.


    Make sense?


    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
You would use something along the lines of (Jan 2023):<\/p>

=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1<\/strong>, IFERROR(YEAR(@cell), 0) = 2023<\/strong>))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108901,"type":"question","name":"Unique codes","excerpt":"Hey all, I am trying to create a system that will check a unique code that is entered through a form and display if this code has been used already or not. I have a formula in the summary sheet that will asses if the code has been used or not: =VLOOKUP($Code$1, {Z Codes Trial Database Range 1}, 3, 0) The problem is that I…","snippet":"Hey all, I am trying to create a system that will check a unique code that is entered through a form and display if this code has been used already or not. I have a formula in the…","categoryID":322,"dateInserted":"2023-08-14T09:42:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T14:37:37+00:00","insertUserID":160845,"insertUser":{"userID":160845,"name":"Itai","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Itai","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3K032BZUIDXO\/nIVF32ENJD2KA.jpeg","dateLastActive":"2023-08-14T14:06:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-14T14:57:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":43,"score":null,"hot":3384032978,"url":"https:\/\/community.smartsheet.com\/discussion\/108901\/unique-codes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108901\/unique-codes","format":"Rich","tagIDs":[204,254],"lastPost":{"discussionID":108901,"commentID":390453,"name":"Re: Unique codes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390453#Comment_390453","dateInserted":"2023-08-14T14:37:37+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-14T14:57:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/RGMU4LB8MFBS\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-14T12:23:27+00:00","dateAnswered":"2023-08-14T11:59:02+00:00","acceptedAnswers":[{"commentID":390433,"body":"

Try nesting in an INDEX function with a specified row of 1.<\/p>

=VLOOKUP(INDEX(Code:Code, 1)<\/strong>, {Cross Sheet Reference}, .......)<\/p>"},{"commentID":390435,"body":"

Try using INDEX(Code:Code, 1) instead of $Code$1<\/p>

Note: Also, I would recommend you use INDEX\/MATCH instead of VLOOKUP. Much more reliable.<\/p>


<\/p>

I hope this helps you.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108902,"type":"question","name":"Average with criteria","excerpt":"Hi I need some help regarding making an average. There is multiple rows with the text \"Mekanisk\" in it at \"System\" column, and a corresponding \"%\" at the \"Fullført\" colum. How do I make an average of all these uniqe % values? Thank's! :)","snippet":"Hi I need some help regarding making an average. There is multiple rows with the text \"Mekanisk\" in it at \"System\" column, and a corresponding \"%\" at the \"Fullført\" colum. How do…","categoryID":322,"dateInserted":"2023-08-14T09:59:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T10:52:15+00:00","insertUserID":165045,"insertUser":{"userID":165045,"name":"TorAtle Myrmel","title":"Planner","url":"https:\/\/community.smartsheet.com\/profile\/TorAtle%20Myrmel","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DsVQA8BS3SQ!clfQx3iu-bw!1fQKS1hybet","dateLastActive":"2023-08-14T12:26:01+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T15:13:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3384019296,"url":"https:\/\/community.smartsheet.com\/discussion\/108902\/average-with-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108902\/average-with-criteria","format":"Rich","lastPost":{"discussionID":108902,"commentID":390420,"name":"Re: Average with criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390420#Comment_390420","dateInserted":"2023-08-14T10:52:15+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T15:13:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/B8WAXBBWC0OC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-14T10:47:17+00:00","dateAnswered":"2023-08-14T10:42:33+00:00","acceptedAnswers":[{"commentID":390418,"body":"

Hi @TorAtle Myrmel<\/a>,<\/p>

If you're after an average for all the %ages in \"Fullført\" against rows containing \"Mekanisk\" in \"System\" then you would use the following formula:<\/p>

=AVERAGEIF([System]:[System], CONTAINS(\"Mekanisk\", @cell), [Fullført]:[Fullført])<\/p>

Example:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps, but if you've any questions or comments then just post! 😊<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Calculate # of Incidents Within A Month

    I have a sheet that is recording data, we have a field for the date of an incident. I have a Roll Up sheet that is counting the data from the main one, with a number of different items. Is there a way to have a formula that counts the # of rows that have a date in a specific month? I want to be able to pull the number of…

  • Unique codes

    Hey all, I am trying to create a system that will check a unique code that is entered through a form and display if this code has been used already or not. I have a formula in the summary sheet that will asses if the code has been used or not: =VLOOKUP($Code$1, {Z Codes Trial Database Range 1}, 3, 0) The problem is that I…

  • Average with criteria

    Hi I need some help regarding making an average. There is multiple rows with the text "Mekanisk" in it at "System" column, and a corresponding "%" at the "Fullført" colum. How do I make an average of all these uniqe % values? Thank's! :)