Remove text up to (inclusive) second to last period (or `://` if only one period)












0















I have the following data:



http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com


What I'm trying to do is get the text up to the second to last period. for example, the formula should return these values:



10.com
12340.com
1123123rs.com
12321.com


Note: If there is only one period (as in the last example) I want to get rid of everything up to (inclusive) the ://.



I tried: (value is stored in D2 cell)



=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1) (which doesn't work well when using RIGHT, but it does works perfectly when using LEFT)



=RIGHT(D2,FIND(".",D2)+1)



I don't know what am missing...



Can someone help?










share|improve this question




















  • 2





    Your examples conflict with your description of the problem.

    – AFH
    Dec 19 '18 at 23:25











  • From the second period from left, or from right?

    – MarianD
    Dec 20 '18 at 0:23






  • 1





    @MarianD To add on to MarianD's question, and what do you want when there is only one period?

    – Rey Juna
    Dec 20 '18 at 0:25











  • So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

    – 3D1T0R
    Dec 20 '18 at 0:43













  • arenumbers.com is the output that I am looking for.

    – MrServer
    Dec 20 '18 at 1:02
















0















I have the following data:



http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com


What I'm trying to do is get the text up to the second to last period. for example, the formula should return these values:



10.com
12340.com
1123123rs.com
12321.com


Note: If there is only one period (as in the last example) I want to get rid of everything up to (inclusive) the ://.



I tried: (value is stored in D2 cell)



=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1) (which doesn't work well when using RIGHT, but it does works perfectly when using LEFT)



=RIGHT(D2,FIND(".",D2)+1)



I don't know what am missing...



Can someone help?










share|improve this question




















  • 2





    Your examples conflict with your description of the problem.

    – AFH
    Dec 19 '18 at 23:25











  • From the second period from left, or from right?

    – MarianD
    Dec 20 '18 at 0:23






  • 1





    @MarianD To add on to MarianD's question, and what do you want when there is only one period?

    – Rey Juna
    Dec 20 '18 at 0:25











  • So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

    – 3D1T0R
    Dec 20 '18 at 0:43













  • arenumbers.com is the output that I am looking for.

    – MrServer
    Dec 20 '18 at 1:02














0












0








0








I have the following data:



http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com


What I'm trying to do is get the text up to the second to last period. for example, the formula should return these values:



10.com
12340.com
1123123rs.com
12321.com


Note: If there is only one period (as in the last example) I want to get rid of everything up to (inclusive) the ://.



I tried: (value is stored in D2 cell)



=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1) (which doesn't work well when using RIGHT, but it does works perfectly when using LEFT)



=RIGHT(D2,FIND(".",D2)+1)



I don't know what am missing...



Can someone help?










share|improve this question
















I have the following data:



http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com


What I'm trying to do is get the text up to the second to last period. for example, the formula should return these values:



10.com
12340.com
1123123rs.com
12321.com


Note: If there is only one period (as in the last example) I want to get rid of everything up to (inclusive) the ://.



I tried: (value is stored in D2 cell)



=RIGHT(D2,FIND(".",D2,FIND(".",D2)+1)-1) (which doesn't work well when using RIGHT, but it does works perfectly when using LEFT)



=RIGHT(D2,FIND(".",D2)+1)



I don't know what am missing...



Can someone help?







microsoft-excel microsoft-excel-2016






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 20 '18 at 17:38









3D1T0R

604111




604111










asked Dec 19 '18 at 22:50









MrServerMrServer

32




32








  • 2





    Your examples conflict with your description of the problem.

    – AFH
    Dec 19 '18 at 23:25











  • From the second period from left, or from right?

    – MarianD
    Dec 20 '18 at 0:23






  • 1





    @MarianD To add on to MarianD's question, and what do you want when there is only one period?

    – Rey Juna
    Dec 20 '18 at 0:25











  • So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

    – 3D1T0R
    Dec 20 '18 at 0:43













  • arenumbers.com is the output that I am looking for.

    – MrServer
    Dec 20 '18 at 1:02














  • 2





    Your examples conflict with your description of the problem.

    – AFH
    Dec 19 '18 at 23:25











  • From the second period from left, or from right?

    – MarianD
    Dec 20 '18 at 0:23






  • 1





    @MarianD To add on to MarianD's question, and what do you want when there is only one period?

    – Rey Juna
    Dec 20 '18 at 0:25











  • So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

    – 3D1T0R
    Dec 20 '18 at 0:43













  • arenumbers.com is the output that I am looking for.

    – MrServer
    Dec 20 '18 at 1:02








2




2





Your examples conflict with your description of the problem.

– AFH
Dec 19 '18 at 23:25





Your examples conflict with your description of the problem.

– AFH
Dec 19 '18 at 23:25













From the second period from left, or from right?

– MarianD
Dec 20 '18 at 0:23





From the second period from left, or from right?

– MarianD
Dec 20 '18 at 0:23




1




1





@MarianD To add on to MarianD's question, and what do you want when there is only one period?

– Rey Juna
Dec 20 '18 at 0:25





@MarianD To add on to MarianD's question, and what do you want when there is only one period?

– Rey Juna
Dec 20 '18 at 0:25













So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

– 3D1T0R
Dec 20 '18 at 0:43







So, just to verify ... Do you want the text after the second period from the left, or do you want the text after the second period from the right? i.e. Does http://0.1.2.3.4.5.6.7.8.9.arenumbers.com become 2.3.4.5.6.7.8.9.arenumbers.com or does it become arenumbers.com?

– 3D1T0R
Dec 20 '18 at 0:43















arenumbers.com is the output that I am looking for.

– MrServer
Dec 20 '18 at 1:02





arenumbers.com is the output that I am looking for.

– MrServer
Dec 20 '18 at 1:02










4 Answers
4






active

oldest

votes


















1














Pretty sure the following formula will do what you want:



Brief summary of algorithm:



use the formula evaluation tool to tease it apart and see how it works in detail. As noted in the comments, you will see that the 99 puts a limit on the number of characters you can return. This might have to be altered if your returned data might be longer




  • remove everything up to and including ://

  • replace the 2nd from last dot with a rarely used character

  • use the FIND function to locate that substitution so as to generate the Start number for the MID function




=MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)


enter image description here






share|improve this answer


























  • Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

    – 3D1T0R
    Dec 20 '18 at 1:54













  • @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

    – Ron Rosenfeld
    Dec 20 '18 at 2:16











  • I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

    – 3D1T0R
    Dec 20 '18 at 17:15











  • @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

    – Ron Rosenfeld
    Dec 20 '18 at 20:20






  • 1





    You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

    – 3D1T0R
    Dec 20 '18 at 21:39



















0














=RIGHT(D2, 
LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))




The explanation:



First, we get the number of periods with this simple trick:




  1. We determine the length of the the full text:
    =LEN(D2) (see column E in the picture)

  2. We determine the length of the same text but with all periods removed:
    =LEN(SUBSTITUTE(D2, ".", "")) (see column F in the picture)

  3. The difference will be the number of periods:
    =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (see column G in the picture)


Second, as we know the number of periods, we are able to determine the
occurrence number of the last but one period. For example, if number of periods
is 5, the last but one period has the occurrence number 4:





  • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (see column H in the picture)


Third, we use this as the 4-th parameter of the SUBSTITUTE() function
to replace that occurrence of period with the symbol:





  • =SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (see column I in the picture)


Fourth, we determine the position of that symbol () with the FIND() function:





  • =FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column J in the picture)


Fifth, as we know the position of that symbol () and the length of the full text,
we may determine the number of remained symbols, i. e. symbols after . For examle,
if the length of the full text is 5 and the position of is 3, there are 2 remained symbols:





  • =LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column K in the picture)


Finally, we use it as the second parameter of the RIGHT() function:





  • =RIGHT(D2, LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column L in the picture)


and it is the final formula.



enter image description here





Note:



The last example in the question has no second but one period (in contradiction with the title of the question). To include such possibility, add . after http://, i. e. substitute all D2 in formula with



=SUBSTITUTE(D2, "//", "//.")





share|improve this answer


























  • Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

    – 3D1T0R
    Dec 20 '18 at 18:37













  • @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

    – MarianD
    Dec 20 '18 at 19:41













  • True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

    – 3D1T0R
    Dec 20 '18 at 19:50





















0














=IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))



How does this work?



First we have an IF statement that lets us treat things differently if there's one (or less) periods, or if there are at least 2 periods.



Our logical test is to check if there's a second period: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))

This will return 0 if it can't find 2 periods in the string, or 1) if there are 2 or more.



We then put the 'if there are 2 or more periods' formula first, and the 'if there are less than 2 periods' formula second, but since the latter is much simpler, I'll cover it first.



If there are less than 2 periods:



If there is one or zero periods, then we need to remove the text up to and including the :// at the beginning, so we'll find the position of the :// and take only the text after it: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)

Just in case there is data that doesn't start with a protocol ending in :// we should wrap this with an IFERROR and get the whole original string instead of a #VALUE error, this ends up being: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)



If there are (at least) 2 periods



Now that we have the simpler cases out of the way, lets look at what happens when there are 2 (or more) periods: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")



Let's break that down:




  1. We replace all periods with a large number of spaces (as many as there are characters in the whole original string): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))

  2. We take only the end of that, specifically twice the length of the original string. This gives us the last two parts, and a bunch of spaces before and between the 2 parts: RIGHT(<step 1>, LEN(A1)*2)

    Note: If you wanted to include the next section as well, you'd change *2 to *3.

  3. We remove all the extraneous spaces using TRIM, leaving us with only a single space, where the one remaining period is supposed to be: TRIM(<step 2>)

  4. We replace that one remaining space with a .: SUBSTITUTE(<step 3>, " ", ".")


So our whole formula is:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))






share|improve this answer
























  • You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

    – 3D1T0R
    Dec 20 '18 at 20:12





















0














The usual formula uses SUBSTITUTE() to replace all the characters of interest, the period in this case, with strings that are arbitrarily long compared to the data one could reasonably find in a cell.



For example, if the longest a set of characters between periods could be is, oh, 95 in your expected data, then a replacement string of, say, 250 "spaces" replacing each period would work nicely.



Then wrap the SUBSTITUTE() function in a RIGHT() function. If your result should only be 3 characters long, you need 253 characters at the right. You have 502 before you pick up anything undesired. If the full 95 expected plus 250 spaces plus another 95 will result, you need 420 characters to cover yourself. But still have 502 you can take without taking undesired characters. So pick a number between the max you expect, 420 and the mas you can use, 502: perhaps 490, and take the rightmost 490 characters with the RIGHT() function.



Now wrap the SUBSTITUTE() function around that, this time replacing spaces with "" so all the spaces introduced go away and you have your desired result.



If spaces can be in the desired result, use a different character which won't be. Something odd looking in the font's list of possible characters. Perhaps ¬ (Alt-01452). Or don't risk it and use that instead of ever using spaces.



Don't type out 250 spaces either. Use the REPT() function in the first (interior) SUBSTITUTE() function to type the character of choice once but get 250 of them!



There are other approaches but they usually involve knowing how many of the character there can be. For instance, your data could be well-formed enough to know there are always 4 periods. Then these work nicely. But if it could vary, 3 here, 7 there, well...



The above is "brutal" rather than "elegant" but who really cares? Simple in conception so easy to use and it does your trick.






share|improve this answer

























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1386081%2fremove-text-up-to-inclusive-second-to-last-period-or-if-only-one-period%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    4 Answers
    4






    active

    oldest

    votes








    4 Answers
    4






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    Pretty sure the following formula will do what you want:



    Brief summary of algorithm:



    use the formula evaluation tool to tease it apart and see how it works in detail. As noted in the comments, you will see that the 99 puts a limit on the number of characters you can return. This might have to be altered if your returned data might be longer




    • remove everything up to and including ://

    • replace the 2nd from last dot with a rarely used character

    • use the FIND function to locate that substitution so as to generate the Start number for the MID function




    =MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)


    enter image description here






    share|improve this answer


























    • Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

      – 3D1T0R
      Dec 20 '18 at 1:54













    • @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

      – Ron Rosenfeld
      Dec 20 '18 at 2:16











    • I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

      – 3D1T0R
      Dec 20 '18 at 17:15











    • @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

      – Ron Rosenfeld
      Dec 20 '18 at 20:20






    • 1





      You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

      – 3D1T0R
      Dec 20 '18 at 21:39
















    1














    Pretty sure the following formula will do what you want:



    Brief summary of algorithm:



    use the formula evaluation tool to tease it apart and see how it works in detail. As noted in the comments, you will see that the 99 puts a limit on the number of characters you can return. This might have to be altered if your returned data might be longer




    • remove everything up to and including ://

    • replace the 2nd from last dot with a rarely used character

    • use the FIND function to locate that substitution so as to generate the Start number for the MID function




    =MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)


    enter image description here






    share|improve this answer


























    • Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

      – 3D1T0R
      Dec 20 '18 at 1:54













    • @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

      – Ron Rosenfeld
      Dec 20 '18 at 2:16











    • I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

      – 3D1T0R
      Dec 20 '18 at 17:15











    • @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

      – Ron Rosenfeld
      Dec 20 '18 at 20:20






    • 1





      You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

      – 3D1T0R
      Dec 20 '18 at 21:39














    1












    1








    1







    Pretty sure the following formula will do what you want:



    Brief summary of algorithm:



    use the formula evaluation tool to tease it apart and see how it works in detail. As noted in the comments, you will see that the 99 puts a limit on the number of characters you can return. This might have to be altered if your returned data might be longer




    • remove everything up to and including ://

    • replace the 2nd from last dot with a rarely used character

    • use the FIND function to locate that substitution so as to generate the Start number for the MID function




    =MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)


    enter image description here






    share|improve this answer















    Pretty sure the following formula will do what you want:



    Brief summary of algorithm:



    use the formula evaluation tool to tease it apart and see how it works in detail. As noted in the comments, you will see that the 99 puts a limit on the number of characters you can return. This might have to be altered if your returned data might be longer




    • remove everything up to and including ://

    • replace the 2nd from last dot with a rarely used character

    • use the FIND function to locate that substitution so as to generate the Start number for the MID function




    =MID(A1,FIND("://",A1)+2+FIND(CHAR(1),SUBSTITUTE("." &MID(A1,FIND("://",A1)+3,99),".",CHAR(1),LEN("." &A1)-LEN(SUBSTITUTE("." &A1,".",""))-1)),99)


    enter image description here







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 21 '18 at 13:18

























    answered Dec 20 '18 at 1:35









    Ron RosenfeldRon Rosenfeld

    1,9572611




    1,9572611













    • Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

      – 3D1T0R
      Dec 20 '18 at 1:54













    • @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

      – Ron Rosenfeld
      Dec 20 '18 at 2:16











    • I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

      – 3D1T0R
      Dec 20 '18 at 17:15











    • @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

      – Ron Rosenfeld
      Dec 20 '18 at 20:20






    • 1





      You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

      – 3D1T0R
      Dec 20 '18 at 21:39



















    • Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

      – 3D1T0R
      Dec 20 '18 at 1:54













    • @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

      – Ron Rosenfeld
      Dec 20 '18 at 2:16











    • I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

      – 3D1T0R
      Dec 20 '18 at 17:15











    • @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

      – Ron Rosenfeld
      Dec 20 '18 at 20:20






    • 1





      You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

      – 3D1T0R
      Dec 20 '18 at 21:39

















    Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

    – 3D1T0R
    Dec 20 '18 at 1:54







    Your use of CHAR(1) is a good idea, but this doesn't work if the end result is very long, as it truncates it at 99 characters.

    – 3D1T0R
    Dec 20 '18 at 1:54















    @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

    – Ron Rosenfeld
    Dec 20 '18 at 2:16





    @3D1T0R It works for the examples given. If you have longer end results in your own data, you should change that value so the string will not be truncated.

    – Ron Rosenfeld
    Dec 20 '18 at 2:16













    I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

    – 3D1T0R
    Dec 20 '18 at 17:15





    I don't have data that I feel needs this, just pointing out an issue with it so that if someone has very long data they can see that this will not work well for them.

    – 3D1T0R
    Dec 20 '18 at 17:15













    @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

    – Ron Rosenfeld
    Dec 20 '18 at 20:20





    @3D1T0R OK. I have different expectations of the audience. I think your comment might have been useful had you written, instead, something indicating that that parameter might need to be adjusted for very long data. Instead, I indicated that the reader should use the formula evaluation tool to examine the specifics. By doing so, they should be able to figure that out for themselves.

    – Ron Rosenfeld
    Dec 20 '18 at 20:20




    1




    1





    You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

    – 3D1T0R
    Dec 20 '18 at 21:39





    You could easily fix the issue I was pointing out by replacing 99 with LEN(A1), then the maximum length of the result would be the length of the original text, and thus will always be at least as long as (actually longer than) the actual result.

    – 3D1T0R
    Dec 20 '18 at 21:39













    0














    =RIGHT(D2, 
    LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))




    The explanation:



    First, we get the number of periods with this simple trick:




    1. We determine the length of the the full text:
      =LEN(D2) (see column E in the picture)

    2. We determine the length of the same text but with all periods removed:
      =LEN(SUBSTITUTE(D2, ".", "")) (see column F in the picture)

    3. The difference will be the number of periods:
      =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (see column G in the picture)


    Second, as we know the number of periods, we are able to determine the
    occurrence number of the last but one period. For example, if number of periods
    is 5, the last but one period has the occurrence number 4:





    • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (see column H in the picture)


    Third, we use this as the 4-th parameter of the SUBSTITUTE() function
    to replace that occurrence of period with the symbol:





    • =SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (see column I in the picture)


    Fourth, we determine the position of that symbol () with the FIND() function:





    • =FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column J in the picture)


    Fifth, as we know the position of that symbol () and the length of the full text,
    we may determine the number of remained symbols, i. e. symbols after . For examle,
    if the length of the full text is 5 and the position of is 3, there are 2 remained symbols:





    • =LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column K in the picture)


    Finally, we use it as the second parameter of the RIGHT() function:





    • =RIGHT(D2, LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column L in the picture)


    and it is the final formula.



    enter image description here





    Note:



    The last example in the question has no second but one period (in contradiction with the title of the question). To include such possibility, add . after http://, i. e. substitute all D2 in formula with



    =SUBSTITUTE(D2, "//", "//.")





    share|improve this answer


























    • Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

      – 3D1T0R
      Dec 20 '18 at 18:37













    • @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

      – MarianD
      Dec 20 '18 at 19:41













    • True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

      – 3D1T0R
      Dec 20 '18 at 19:50


















    0














    =RIGHT(D2, 
    LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))




    The explanation:



    First, we get the number of periods with this simple trick:




    1. We determine the length of the the full text:
      =LEN(D2) (see column E in the picture)

    2. We determine the length of the same text but with all periods removed:
      =LEN(SUBSTITUTE(D2, ".", "")) (see column F in the picture)

    3. The difference will be the number of periods:
      =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (see column G in the picture)


    Second, as we know the number of periods, we are able to determine the
    occurrence number of the last but one period. For example, if number of periods
    is 5, the last but one period has the occurrence number 4:





    • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (see column H in the picture)


    Third, we use this as the 4-th parameter of the SUBSTITUTE() function
    to replace that occurrence of period with the symbol:





    • =SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (see column I in the picture)


    Fourth, we determine the position of that symbol () with the FIND() function:





    • =FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column J in the picture)


    Fifth, as we know the position of that symbol () and the length of the full text,
    we may determine the number of remained symbols, i. e. symbols after . For examle,
    if the length of the full text is 5 and the position of is 3, there are 2 remained symbols:





    • =LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column K in the picture)


    Finally, we use it as the second parameter of the RIGHT() function:





    • =RIGHT(D2, LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column L in the picture)


    and it is the final formula.



    enter image description here





    Note:



    The last example in the question has no second but one period (in contradiction with the title of the question). To include such possibility, add . after http://, i. e. substitute all D2 in formula with



    =SUBSTITUTE(D2, "//", "//.")





    share|improve this answer


























    • Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

      – 3D1T0R
      Dec 20 '18 at 18:37













    • @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

      – MarianD
      Dec 20 '18 at 19:41













    • True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

      – 3D1T0R
      Dec 20 '18 at 19:50
















    0












    0








    0







    =RIGHT(D2, 
    LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))




    The explanation:



    First, we get the number of periods with this simple trick:




    1. We determine the length of the the full text:
      =LEN(D2) (see column E in the picture)

    2. We determine the length of the same text but with all periods removed:
      =LEN(SUBSTITUTE(D2, ".", "")) (see column F in the picture)

    3. The difference will be the number of periods:
      =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (see column G in the picture)


    Second, as we know the number of periods, we are able to determine the
    occurrence number of the last but one period. For example, if number of periods
    is 5, the last but one period has the occurrence number 4:





    • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (see column H in the picture)


    Third, we use this as the 4-th parameter of the SUBSTITUTE() function
    to replace that occurrence of period with the symbol:





    • =SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (see column I in the picture)


    Fourth, we determine the position of that symbol () with the FIND() function:





    • =FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column J in the picture)


    Fifth, as we know the position of that symbol () and the length of the full text,
    we may determine the number of remained symbols, i. e. symbols after . For examle,
    if the length of the full text is 5 and the position of is 3, there are 2 remained symbols:





    • =LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column K in the picture)


    Finally, we use it as the second parameter of the RIGHT() function:





    • =RIGHT(D2, LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column L in the picture)


    and it is the final formula.



    enter image description here





    Note:



    The last example in the question has no second but one period (in contradiction with the title of the question). To include such possibility, add . after http://, i. e. substitute all D2 in formula with



    =SUBSTITUTE(D2, "//", "//.")





    share|improve this answer















    =RIGHT(D2, 
    LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1))




    The explanation:



    First, we get the number of periods with this simple trick:




    1. We determine the length of the the full text:
      =LEN(D2) (see column E in the picture)

    2. We determine the length of the same text but with all periods removed:
      =LEN(SUBSTITUTE(D2, ".", "")) (see column F in the picture)

    3. The difference will be the number of periods:
      =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) (see column G in the picture)


    Second, as we know the number of periods, we are able to determine the
    occurrence number of the last but one period. For example, if number of periods
    is 5, the last but one period has the occurrence number 4:





    • =LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1 (see column H in the picture)


    Third, we use this as the 4-th parameter of the SUBSTITUTE() function
    to replace that occurrence of period with the symbol:





    • =SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1) (see column I in the picture)


    Fourth, we determine the position of that symbol () with the FIND() function:





    • =FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column J in the picture)


    Fifth, as we know the position of that symbol () and the length of the full text,
    we may determine the number of remained symbols, i. e. symbols after . For examle,
    if the length of the full text is 5 and the position of is 3, there are 2 remained symbols:





    • =LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column K in the picture)


    Finally, we use it as the second parameter of the RIGHT() function:





    • =RIGHT(D2, LEN(D2) - FIND("", SUBSTITUTE(D2, ".", "", LEN(D2) - LEN(SUBSTITUTE(D2, ".", "")) - 1)) (see column L in the picture)


    and it is the final formula.



    enter image description here





    Note:



    The last example in the question has no second but one period (in contradiction with the title of the question). To include such possibility, add . after http://, i. e. substitute all D2 in formula with



    =SUBSTITUTE(D2, "//", "//.")






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Dec 20 '18 at 16:01

























    answered Dec 20 '18 at 0:14









    MarianDMarianD

    1,4231518




    1,4231518













    • Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

      – 3D1T0R
      Dec 20 '18 at 18:37













    • @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

      – MarianD
      Dec 20 '18 at 19:41













    • True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

      – 3D1T0R
      Dec 20 '18 at 19:50





















    • Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

      – 3D1T0R
      Dec 20 '18 at 18:37













    • @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

      – MarianD
      Dec 20 '18 at 19:41













    • True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

      – 3D1T0R
      Dec 20 '18 at 19:50



















    Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

    – 3D1T0R
    Dec 20 '18 at 18:37







    Note: This will mess up if your data contains characters after the ://. If you use this formula, I recommend using @RonRosenfeld's trick of using CHAR(1) instead of , as it's much less likely that your data is going to contain the "Start of Heading" character than a .

    – 3D1T0R
    Dec 20 '18 at 18:37















    @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

    – MarianD
    Dec 20 '18 at 19:41







    @3D1T0R, the backslash character () is not allowed in URIs, and so nor in URLs - see Chapter 2 (Characters) in RFC 3986.

    – MarianD
    Dec 20 '18 at 19:41















    True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

    – 3D1T0R
    Dec 20 '18 at 19:50







    True, but that doesn't mean that there can't be backslashes in someone's data. Others may come here in the future that aren't looking to use this with URLs, and they may have backslashes, or other unexpected characters in their data, but it's less likely that they'll have characters that can't be typed directly typed on a keyboard, and very unlikely that they'll specifically have the "Start of Heading" character in their data unless it's corrupted, in which case there are other things they should do with their data before they process it in this way.

    – 3D1T0R
    Dec 20 '18 at 19:50













    0














    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))



    How does this work?



    First we have an IF statement that lets us treat things differently if there's one (or less) periods, or if there are at least 2 periods.



    Our logical test is to check if there's a second period: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))

    This will return 0 if it can't find 2 periods in the string, or 1) if there are 2 or more.



    We then put the 'if there are 2 or more periods' formula first, and the 'if there are less than 2 periods' formula second, but since the latter is much simpler, I'll cover it first.



    If there are less than 2 periods:



    If there is one or zero periods, then we need to remove the text up to and including the :// at the beginning, so we'll find the position of the :// and take only the text after it: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)

    Just in case there is data that doesn't start with a protocol ending in :// we should wrap this with an IFERROR and get the whole original string instead of a #VALUE error, this ends up being: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)



    If there are (at least) 2 periods



    Now that we have the simpler cases out of the way, lets look at what happens when there are 2 (or more) periods: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")



    Let's break that down:




    1. We replace all periods with a large number of spaces (as many as there are characters in the whole original string): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))

    2. We take only the end of that, specifically twice the length of the original string. This gives us the last two parts, and a bunch of spaces before and between the 2 parts: RIGHT(<step 1>, LEN(A1)*2)

      Note: If you wanted to include the next section as well, you'd change *2 to *3.

    3. We remove all the extraneous spaces using TRIM, leaving us with only a single space, where the one remaining period is supposed to be: TRIM(<step 2>)

    4. We replace that one remaining space with a .: SUBSTITUTE(<step 3>, " ", ".")


    So our whole formula is:
    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))






    share|improve this answer
























    • You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

      – 3D1T0R
      Dec 20 '18 at 20:12


















    0














    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))



    How does this work?



    First we have an IF statement that lets us treat things differently if there's one (or less) periods, or if there are at least 2 periods.



    Our logical test is to check if there's a second period: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))

    This will return 0 if it can't find 2 periods in the string, or 1) if there are 2 or more.



    We then put the 'if there are 2 or more periods' formula first, and the 'if there are less than 2 periods' formula second, but since the latter is much simpler, I'll cover it first.



    If there are less than 2 periods:



    If there is one or zero periods, then we need to remove the text up to and including the :// at the beginning, so we'll find the position of the :// and take only the text after it: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)

    Just in case there is data that doesn't start with a protocol ending in :// we should wrap this with an IFERROR and get the whole original string instead of a #VALUE error, this ends up being: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)



    If there are (at least) 2 periods



    Now that we have the simpler cases out of the way, lets look at what happens when there are 2 (or more) periods: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")



    Let's break that down:




    1. We replace all periods with a large number of spaces (as many as there are characters in the whole original string): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))

    2. We take only the end of that, specifically twice the length of the original string. This gives us the last two parts, and a bunch of spaces before and between the 2 parts: RIGHT(<step 1>, LEN(A1)*2)

      Note: If you wanted to include the next section as well, you'd change *2 to *3.

    3. We remove all the extraneous spaces using TRIM, leaving us with only a single space, where the one remaining period is supposed to be: TRIM(<step 2>)

    4. We replace that one remaining space with a .: SUBSTITUTE(<step 3>, " ", ".")


    So our whole formula is:
    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))






    share|improve this answer
























    • You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

      – 3D1T0R
      Dec 20 '18 at 20:12
















    0












    0








    0







    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))



    How does this work?



    First we have an IF statement that lets us treat things differently if there's one (or less) periods, or if there are at least 2 periods.



    Our logical test is to check if there's a second period: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))

    This will return 0 if it can't find 2 periods in the string, or 1) if there are 2 or more.



    We then put the 'if there are 2 or more periods' formula first, and the 'if there are less than 2 periods' formula second, but since the latter is much simpler, I'll cover it first.



    If there are less than 2 periods:



    If there is one or zero periods, then we need to remove the text up to and including the :// at the beginning, so we'll find the position of the :// and take only the text after it: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)

    Just in case there is data that doesn't start with a protocol ending in :// we should wrap this with an IFERROR and get the whole original string instead of a #VALUE error, this ends up being: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)



    If there are (at least) 2 periods



    Now that we have the simpler cases out of the way, lets look at what happens when there are 2 (or more) periods: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")



    Let's break that down:




    1. We replace all periods with a large number of spaces (as many as there are characters in the whole original string): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))

    2. We take only the end of that, specifically twice the length of the original string. This gives us the last two parts, and a bunch of spaces before and between the 2 parts: RIGHT(<step 1>, LEN(A1)*2)

      Note: If you wanted to include the next section as well, you'd change *2 to *3.

    3. We remove all the extraneous spaces using TRIM, leaving us with only a single space, where the one remaining period is supposed to be: TRIM(<step 2>)

    4. We replace that one remaining space with a .: SUBSTITUTE(<step 3>, " ", ".")


    So our whole formula is:
    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))






    share|improve this answer













    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))



    How does this work?



    First we have an IF statement that lets us treat things differently if there's one (or less) periods, or if there are at least 2 periods.



    Our logical test is to check if there's a second period: LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2))

    This will return 0 if it can't find 2 periods in the string, or 1) if there are 2 or more.



    We then put the 'if there are 2 or more periods' formula first, and the 'if there are less than 2 periods' formula second, but since the latter is much simpler, I'll cover it first.



    If there are less than 2 periods:



    If there is one or zero periods, then we need to remove the text up to and including the :// at the beginning, so we'll find the position of the :// and take only the text after it: RIGHT(A1, LEN(A1)-FIND("://", A1)-2)

    Just in case there is data that doesn't start with a protocol ending in :// we should wrap this with an IFERROR and get the whole original string instead of a #VALUE error, this ends up being: IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1)



    If there are (at least) 2 periods



    Now that we have the simpler cases out of the way, lets look at what happens when there are 2 (or more) periods: SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", ".")



    Let's break that down:




    1. We replace all periods with a large number of spaces (as many as there are characters in the whole original string): SUBSTITUTE(A1, ".", REPT(" ", LEN(A1)))

    2. We take only the end of that, specifically twice the length of the original string. This gives us the last two parts, and a bunch of spaces before and between the 2 parts: RIGHT(<step 1>, LEN(A1)*2)

      Note: If you wanted to include the next section as well, you'd change *2 to *3.

    3. We remove all the extraneous spaces using TRIM, leaving us with only a single space, where the one remaining period is supposed to be: TRIM(<step 2>)

    4. We replace that one remaining space with a .: SUBSTITUTE(<step 3>, " ", ".")


    So our whole formula is:
    =IF(LEN(A1)-LEN(SUBSTITUTE(A1, ".", "", 2)), SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, ".", REPT(" ", LEN(A1))), LEN(A1)*2)), " ", "."), IFERROR(RIGHT(A1, LEN(A1)-FIND("://", A1)-2), A1))







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Dec 20 '18 at 18:16









    3D1T0R3D1T0R

    604111




    604111













    • You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

      – 3D1T0R
      Dec 20 '18 at 20:12





















    • You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

      – 3D1T0R
      Dec 20 '18 at 20:12



















    You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

    – 3D1T0R
    Dec 20 '18 at 20:12







    You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing " " with CHAR(172) in both places where it is used, thus preventing some issues that could occur due to data containing spaces. (Note: There could still be some issues if your results needs to contain multiple spaces next to each other.)

    – 3D1T0R
    Dec 20 '18 at 20:12













    0














    The usual formula uses SUBSTITUTE() to replace all the characters of interest, the period in this case, with strings that are arbitrarily long compared to the data one could reasonably find in a cell.



    For example, if the longest a set of characters between periods could be is, oh, 95 in your expected data, then a replacement string of, say, 250 "spaces" replacing each period would work nicely.



    Then wrap the SUBSTITUTE() function in a RIGHT() function. If your result should only be 3 characters long, you need 253 characters at the right. You have 502 before you pick up anything undesired. If the full 95 expected plus 250 spaces plus another 95 will result, you need 420 characters to cover yourself. But still have 502 you can take without taking undesired characters. So pick a number between the max you expect, 420 and the mas you can use, 502: perhaps 490, and take the rightmost 490 characters with the RIGHT() function.



    Now wrap the SUBSTITUTE() function around that, this time replacing spaces with "" so all the spaces introduced go away and you have your desired result.



    If spaces can be in the desired result, use a different character which won't be. Something odd looking in the font's list of possible characters. Perhaps ¬ (Alt-01452). Or don't risk it and use that instead of ever using spaces.



    Don't type out 250 spaces either. Use the REPT() function in the first (interior) SUBSTITUTE() function to type the character of choice once but get 250 of them!



    There are other approaches but they usually involve knowing how many of the character there can be. For instance, your data could be well-formed enough to know there are always 4 periods. Then these work nicely. But if it could vary, 3 here, 7 there, well...



    The above is "brutal" rather than "elegant" but who really cares? Simple in conception so easy to use and it does your trick.






    share|improve this answer






























      0














      The usual formula uses SUBSTITUTE() to replace all the characters of interest, the period in this case, with strings that are arbitrarily long compared to the data one could reasonably find in a cell.



      For example, if the longest a set of characters between periods could be is, oh, 95 in your expected data, then a replacement string of, say, 250 "spaces" replacing each period would work nicely.



      Then wrap the SUBSTITUTE() function in a RIGHT() function. If your result should only be 3 characters long, you need 253 characters at the right. You have 502 before you pick up anything undesired. If the full 95 expected plus 250 spaces plus another 95 will result, you need 420 characters to cover yourself. But still have 502 you can take without taking undesired characters. So pick a number between the max you expect, 420 and the mas you can use, 502: perhaps 490, and take the rightmost 490 characters with the RIGHT() function.



      Now wrap the SUBSTITUTE() function around that, this time replacing spaces with "" so all the spaces introduced go away and you have your desired result.



      If spaces can be in the desired result, use a different character which won't be. Something odd looking in the font's list of possible characters. Perhaps ¬ (Alt-01452). Or don't risk it and use that instead of ever using spaces.



      Don't type out 250 spaces either. Use the REPT() function in the first (interior) SUBSTITUTE() function to type the character of choice once but get 250 of them!



      There are other approaches but they usually involve knowing how many of the character there can be. For instance, your data could be well-formed enough to know there are always 4 periods. Then these work nicely. But if it could vary, 3 here, 7 there, well...



      The above is "brutal" rather than "elegant" but who really cares? Simple in conception so easy to use and it does your trick.






      share|improve this answer




























        0












        0








        0







        The usual formula uses SUBSTITUTE() to replace all the characters of interest, the period in this case, with strings that are arbitrarily long compared to the data one could reasonably find in a cell.



        For example, if the longest a set of characters between periods could be is, oh, 95 in your expected data, then a replacement string of, say, 250 "spaces" replacing each period would work nicely.



        Then wrap the SUBSTITUTE() function in a RIGHT() function. If your result should only be 3 characters long, you need 253 characters at the right. You have 502 before you pick up anything undesired. If the full 95 expected plus 250 spaces plus another 95 will result, you need 420 characters to cover yourself. But still have 502 you can take without taking undesired characters. So pick a number between the max you expect, 420 and the mas you can use, 502: perhaps 490, and take the rightmost 490 characters with the RIGHT() function.



        Now wrap the SUBSTITUTE() function around that, this time replacing spaces with "" so all the spaces introduced go away and you have your desired result.



        If spaces can be in the desired result, use a different character which won't be. Something odd looking in the font's list of possible characters. Perhaps ¬ (Alt-01452). Or don't risk it and use that instead of ever using spaces.



        Don't type out 250 spaces either. Use the REPT() function in the first (interior) SUBSTITUTE() function to type the character of choice once but get 250 of them!



        There are other approaches but they usually involve knowing how many of the character there can be. For instance, your data could be well-formed enough to know there are always 4 periods. Then these work nicely. But if it could vary, 3 here, 7 there, well...



        The above is "brutal" rather than "elegant" but who really cares? Simple in conception so easy to use and it does your trick.






        share|improve this answer















        The usual formula uses SUBSTITUTE() to replace all the characters of interest, the period in this case, with strings that are arbitrarily long compared to the data one could reasonably find in a cell.



        For example, if the longest a set of characters between periods could be is, oh, 95 in your expected data, then a replacement string of, say, 250 "spaces" replacing each period would work nicely.



        Then wrap the SUBSTITUTE() function in a RIGHT() function. If your result should only be 3 characters long, you need 253 characters at the right. You have 502 before you pick up anything undesired. If the full 95 expected plus 250 spaces plus another 95 will result, you need 420 characters to cover yourself. But still have 502 you can take without taking undesired characters. So pick a number between the max you expect, 420 and the mas you can use, 502: perhaps 490, and take the rightmost 490 characters with the RIGHT() function.



        Now wrap the SUBSTITUTE() function around that, this time replacing spaces with "" so all the spaces introduced go away and you have your desired result.



        If spaces can be in the desired result, use a different character which won't be. Something odd looking in the font's list of possible characters. Perhaps ¬ (Alt-01452). Or don't risk it and use that instead of ever using spaces.



        Don't type out 250 spaces either. Use the REPT() function in the first (interior) SUBSTITUTE() function to type the character of choice once but get 250 of them!



        There are other approaches but they usually involve knowing how many of the character there can be. For instance, your data could be well-formed enough to know there are always 4 periods. Then these work nicely. But if it could vary, 3 here, 7 there, well...



        The above is "brutal" rather than "elegant" but who really cares? Simple in conception so easy to use and it does your trick.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 20 '18 at 20:20









        Blackwood

        2,88861728




        2,88861728










        answered Dec 20 '18 at 2:16









        RoyRoy

        1




        1






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1386081%2fremove-text-up-to-inclusive-second-to-last-period-or-if-only-one-period%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            "Incorrect syntax near the keyword 'ON'. (on update cascade, on delete cascade,)

            Alcedinidae

            RAC Tourist Trophy