Remove text up to (inclusive) second to last period (or `://` if only one period)
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
|
show 4 more comments
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
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. Doeshttp://0.1.2.3.4.5.6.7.8.9.arenumbers.com
become2.3.4.5.6.7.8.9.arenumbers.com
or does it becomearenumbers.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
|
show 4 more comments
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
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
microsoft-excel microsoft-excel-2016
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. Doeshttp://0.1.2.3.4.5.6.7.8.9.arenumbers.com
become2.3.4.5.6.7.8.9.arenumbers.com
or does it becomearenumbers.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
|
show 4 more comments
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. Doeshttp://0.1.2.3.4.5.6.7.8.9.arenumbers.com
become2.3.4.5.6.7.8.9.arenumbers.com
or does it becomearenumbers.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
|
show 4 more comments
4 Answers
4
active
oldest
votes
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 theMID
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)
Your use ofCHAR(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 replacing99
withLEN(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
|
show 3 more comments
=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:
- We determine the length of the the full text:
=LEN(D2)
(see columnE
in the picture)
- We determine the length of the same text but with all periods removed:
=LEN(SUBSTITUTE(D2, ".", ""))
(see columnF
in the picture)
- The difference will be the number of periods:
=LEN(D2) - LEN(SUBSTITUTE(D2, ".", ""))
(see columnG
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 columnH
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 columnI
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 columnJ
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 columnK
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 columnL
in the picture)
and it is the final formula.
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, "//", "//.")
Note: This will mess up if your data containscharacters after the
://
. If you use this formula, I recommend using @RonRosenfeld's trick of usingCHAR(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
add a comment |
=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:
- 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)))
- 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
. - 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>)
- 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))
You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing" "
withCHAR(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
add a comment |
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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 theMID
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)
Your use ofCHAR(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 replacing99
withLEN(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
|
show 3 more comments
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 theMID
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)
Your use ofCHAR(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 replacing99
withLEN(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
|
show 3 more comments
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 theMID
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)
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 theMID
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)
edited Dec 21 '18 at 13:18
answered Dec 20 '18 at 1:35
Ron RosenfeldRon Rosenfeld
1,9572611
1,9572611
Your use ofCHAR(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 replacing99
withLEN(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
|
show 3 more comments
Your use ofCHAR(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 replacing99
withLEN(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
|
show 3 more comments
=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:
- We determine the length of the the full text:
=LEN(D2)
(see columnE
in the picture)
- We determine the length of the same text but with all periods removed:
=LEN(SUBSTITUTE(D2, ".", ""))
(see columnF
in the picture)
- The difference will be the number of periods:
=LEN(D2) - LEN(SUBSTITUTE(D2, ".", ""))
(see columnG
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 columnH
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 columnI
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 columnJ
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 columnK
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 columnL
in the picture)
and it is the final formula.
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, "//", "//.")
Note: This will mess up if your data containscharacters after the
://
. If you use this formula, I recommend using @RonRosenfeld's trick of usingCHAR(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
add a comment |
=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:
- We determine the length of the the full text:
=LEN(D2)
(see columnE
in the picture)
- We determine the length of the same text but with all periods removed:
=LEN(SUBSTITUTE(D2, ".", ""))
(see columnF
in the picture)
- The difference will be the number of periods:
=LEN(D2) - LEN(SUBSTITUTE(D2, ".", ""))
(see columnG
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 columnH
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 columnI
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 columnJ
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 columnK
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 columnL
in the picture)
and it is the final formula.
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, "//", "//.")
Note: This will mess up if your data containscharacters after the
://
. If you use this formula, I recommend using @RonRosenfeld's trick of usingCHAR(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
add a comment |
=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:
- We determine the length of the the full text:
=LEN(D2)
(see columnE
in the picture)
- We determine the length of the same text but with all periods removed:
=LEN(SUBSTITUTE(D2, ".", ""))
(see columnF
in the picture)
- The difference will be the number of periods:
=LEN(D2) - LEN(SUBSTITUTE(D2, ".", ""))
(see columnG
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 columnH
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 columnI
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 columnJ
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 columnK
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 columnL
in the picture)
and it is the final formula.
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, "//", "//.")
=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:
- We determine the length of the the full text:
=LEN(D2)
(see columnE
in the picture)
- We determine the length of the same text but with all periods removed:
=LEN(SUBSTITUTE(D2, ".", ""))
(see columnF
in the picture)
- The difference will be the number of periods:
=LEN(D2) - LEN(SUBSTITUTE(D2, ".", ""))
(see columnG
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 columnH
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 columnI
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 columnJ
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 columnK
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 columnL
in the picture)
and it is the final formula.
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, "//", "//.")
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 containscharacters after the
://
. If you use this formula, I recommend using @RonRosenfeld's trick of usingCHAR(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
add a comment |
Note: This will mess up if your data containscharacters after the
://
. If you use this formula, I recommend using @RonRosenfeld's trick of usingCHAR(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
add a comment |
=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:
- 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)))
- 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
. - 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>)
- 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))
You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing" "
withCHAR(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
add a comment |
=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:
- 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)))
- 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
. - 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>)
- 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))
You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing" "
withCHAR(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
add a comment |
=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:
- 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)))
- 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
. - 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>)
- 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))
=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:
- 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)))
- 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
. - 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>)
- 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))
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" "
withCHAR(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
add a comment |
You can also use the "Not" character instead of the space (as suggested by @Roy in his answer) by replacing" "
withCHAR(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
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
edited Dec 20 '18 at 20:20
Blackwood
2,88861728
2,88861728
answered Dec 20 '18 at 2:16
RoyRoy
1
1
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
become2.3.4.5.6.7.8.9.arenumbers.com
or does it becomearenumbers.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