Forum Discussion

Alessandro_Becci's avatar
Alessandro_Becci
Copper Contributor
Sep 09, 2020
Solved

Trimming formula in Excel

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

 

 

  • Hi Alessandro_Becci,

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

     

     

3 Replies

Resources