Sep 09 2020 06:31 AM
Hello! I have an issue with a trimming formula.
I am trying to trim a string and I want to get anything that is between the first and the last dot of the string. Here is an example: I have www.help.needed.com and I want to get only help.needed.
I have used the following formula:
=IFERROR(LEFT(RIGHT(A2,LEN(A2)-SEARCH(".",A2,SEARCH(".",A2))),LEN(RIGHT(A2,LEN(A2)-SEARCH(".",A2,SEARCH(".",A2))))-FIND(".",A2)),"")
However, whenever the last part of the website is different than ".com" but it is, for example, ".de" it trims also what is before the dot. Here is what I mean
www.help.needed.de --> I get help.neede (it omits the final "d")
I have also tried with
=RIGHT(A2;LEN(A2)-FIND(".";A2)) and then =LEFT(B2;LEN(B2)-FIND(".";B2))
Can anybody help me?
Thank you very much
Sep 09 2020 07:03 AM
SolutionTry this:
=LEFT(TRIM(MID(A2,FIND(".",A2)+1,256)),FIND("~",SUBSTITUTE(MID(A2,FIND(".",A2)+1,256),".","~",LEN(TRIM(MID(A2,FIND(".",A2)+1,256)))-LEN(SUBSTITUTE(TRIM(MID(A2,FIND(".",A2)+1,256)),".",""))))-1)
Sep 09 2020 07:03 AM
SolutionTry this:
=LEFT(TRIM(MID(A2,FIND(".",A2)+1,256)),FIND("~",SUBSTITUTE(MID(A2,FIND(".",A2)+1,256),".","~",LEN(TRIM(MID(A2,FIND(".",A2)+1,256)))-LEN(SUBSTITUTE(TRIM(MID(A2,FIND(".",A2)+1,256)),".",""))))-1)