Removing Everything before Specific String of Characters

[mcgonma]

How do I remove everything in a cell that comes before the final set of characters "..."

In this example of how the data in the cells appears, I would like to have a column next to this that would have just "Melbourne" (if I could eliminate the ending period "." too that would be great.

ACE.AU VIC..Melbourne.

 

[Ron Coderre]

(Your example only has two periods)
Try something like this:

=TRIM(SUBSTITUTE(MID(A1,FIND("..",A1)+2,LEN(A1)),".",""))

Is that something you can work with?

 

[mcgonma]

Thank you!
This works well. It made me notice, however, that in some cases there is only one period before the piece of information that remains. Is there a way to nest another formula with this so that it can look for different variations of the periods (e.g., ".." or "." or "...") before the information I want to keep?

 

[Jborg]

Assuming that Text is in A1. Put this formula in E1

=SUBSTITUTE(A1,MID(A1,1,LARGE(INDEX(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".")*(ROW(INDIRECT("1:"&LEN(A1)))),0),2)),"")

This should work however many periods you have. Always assuming that after Melbourne you always have a period

You do not require CSE. Just Enter

 

[Ron Coderre]

In your example, the text ends with a period and a space.
If that's typical, try this formula:

=MID(LEFT(A1,LEN(A1)-2),FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,LEN(A1))

Dos that help?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...ing-before-specific-string-of-characters.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[mcgonma]

I would like to thank everyone who provided me with solutions, both here and on the ExcelForum. My apologies for double posting; I will not do that again.
I greatly appreciate the services and support received!!!!