SOLVED

Trimming formula in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1648454%22%20slang%3D%22en-US%22%3ETrimming%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648454%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I%20have%20an%20issue%20with%20a%20trimming%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EI%20am%20trying%20to%20trim%20a%20string%20and%20I%20want%20to%20get%20anything%20that%20is%20between%20the%20first%20and%20the%20last%20dot%20of%20the%20string.%20Here%20is%20an%20example%3A%20I%20have%20www.help.needed.com%26nbsp%3Band%20I%20want%20to%20get%20only%20help.needed.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EI%20have%20used%20the%20following%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIFERROR%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ELEFT%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ERIGHT%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ELEN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESEARCH%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22.%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESEARCH%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22.%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ELEN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ERIGHT%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ELEN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESEARCH%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22.%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESEARCH%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22.%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EFIND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22.%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3EA2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EHowever%2C%20whenever%20the%20last%20part%20of%20the%20website%20is%20different%20than%20%22.com%22%20but%20it%20is%2C%20for%20example%2C%20%22.%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Ede%22%20it%20trims%20also%20what%20is%20before%20the%20dot.%20Here%20is%20what%20I%20mean%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3Ewww.help.needed.de%26nbsp%3B--%26gt%3B%20I%20get%20help.neede%20(it%20omits%20the%20final%20%22d%22)%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EI%20have%20also%20tried%20with%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3CSPAN%3E%3DRIGHT(A2%3BLEN(A2)-FIND(%22.%22%3BA2))%20and%20then%26nbsp%3B%3DLEFT(B2%3BLEN(B2)-FIND(%22.%22%3BB2))%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3CSPAN%3ECan%20anybody%20help%20me%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3CSPAN%3EThank%20you%20very%20much%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1648454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1648646%22%20slang%3D%22en-US%22%3ERe%3A%20Trimming%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1648646%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787150%22%20target%3D%22_blank%22%3E%40Alessandro_Becci%3C%2FA%3E%2C%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DLEFT(TRIM(MID(A2%2CFIND(%22.%22%2CA2)%2B1%2C256))%2CFIND(%22~%22%2CSUBSTITUTE(MID(A2%2CFIND(%22.%22%2CA2)%2B1%2C256)%2C%22.%22%2C%22~%22%2CLEN(TRIM(MID(A2%2CFIND(%22.%22%2CA2)%2B1%2C256)))-LEN(SUBSTITUTE(TRIM(MID(A2%2CFIND(%22.%22%2CA2)%2B1%2C256))%2C%22.%22%2C%22%22))))-1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Alessandro_Becci (New 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)

 

 

Highlighted

@Bennadeau 

 

Thanks Ben

Highlighted

@Alessandro_Becci 

You're very welcome!