Hello can someone please tell me how to extract text bettwen two dots.
Example: goldintre.cr.usgs.gov.
I want all text from first dot to the last(without dots at the begining and at the end)
This must be done without removing last dot from source text.
Thank you
You have not provided sufficient details for your data as well as desired output. Let's consider all possibilities then.
Case-1 You always have a . in end, and you want text between first and this last dot. Use this formula -
=MID(A1, SEARCH(".", A1)+1, LEN(A1)-SEARCH(".", A1)-1)
This will give you cr.usgs.gov as output.
Case-2 You need not consider last dot and remove text before that one (assuming again that last dot in last place)
=MID(A1,SEARCH(".",A1)+1, SEARCH("$",SUBSTITUTE(A1,".", "$", LEN(A1)-LEN(SUBSTITUTE(A1, ".", ""))-1))-SEARCH(".",A1)-1)
This will give you cr.usgs as output (also assuming that $ is nowhere present in any of your text strings.
Other cases When you do not have dots in last places everywhere. For this you'll have to tweak your formula a little bit.
=MID(A1,SEARCH(".",A1)+1, SEARCH("$",SUBSTITUTE(A1,".", "$", LEN(A1)-LEN(SUBSTITUTE(A1, ".", ""))))-SEARCH(".",A1)-1)
This will give you output cd.ef if your input (A1) is ab.cd.ef.ghi. In the case-2 formula I just removed extra -1 which was used to exclude last dot from the text string.
Note This formula will work in all versions of excel.