SOLVED

Trimming formula in Excel

Copper Contributor

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

 

 

3 Replies
best response confirmed by Alessandro_Becci (Copper Contributor)
Solution

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)

 

 

@Bennadeau 

 

Thanks Ben ;)

@Alessandro_Becci 

You're very welcome!

1 best response

Accepted Solutions
best response confirmed by Alessandro_Becci (Copper Contributor)
Solution

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)

 

 

View solution in original post