Extract text between 2 periods in string

pelampe
pelampe used Ask the Experts™
on
Hello,

I have a document that has string value in one cell separated by 5 periods.

For example:

Title.State.County.District.TYPE.pdf

1- I need to extract the TYPE value which sits to the right of District values and the left of the pdf values.
2 - The char length of the Districts will vary as well as the char length of the TYPE values.
3 - The only constants here are that there will always be a PERIOD followed by the pdf text (for a total of 4 characters) at the end - AND there will always be 4 PERIODs preceding the desired TYPE value.

The intent is to extract ONLY the TYPE value WITHOUT the PERIOD and the pdf txt superseding.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Assuming the value is in A1:

=MID(A1,FIND("|",SUBSTITUTE(A1,".","|",4))+1,FIND("|",SUBSTITUTE(A1&".",".","|",5))-FIND("|",SUBSTITUTE(A1,".","|",4))-1)

Kevin
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
A somewhat shorter formula, assuming TYPE is less than 99 characters long, is:
=MID(LEFT(A1,LEN(A1)-4),FIND("|",SUBSTITUTE(A1,".","|",4))+1,99)
Most Valuable Expert 2011
Awarded 2010
Commented:
Or

=SUBSTITUTE(MID(A1,FIND("%",SUBSTITUTE(A1,".","%",4))+1,99),".pdf","")
Hello,

Why not just use Text To Columns with a period as the delimiter?

Regards,
Zack Barresse
Top Expert 2008

Commented:
You must have actually read the question!
Sssh!!  Don't say that too loud, people will get the wrong impression.  ;)
pelampeGIS Analyst

Author

Commented:
Thanks for all your inputs.

I messed up earlier and forgot ONE entry between the periods.

So instead of:

Title.State.County.District.TYPE.pdf

It should've been:

Title.State.County.City.District.TYPE.pdf

Because of that, I had to go in manually and increment each counter(?) or placement(?) number - they were all 4 or 5; by +1.  Then they all worked.

The 1st solution was the trickier one (at least for me) because it had both a 4 and a 5 and it took me longer to figure out which ones to switch.

But since they all worked am splitting points to all 3 with some consolation points to the Text to Columns response.  As far as using Text to Columns, that was not an option.  I didn't want to break it into separate columns.  But that is a useful thing to know.

Cheers.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Phil,
These are not easy formulas to figure out if you haven't worked with them before. If you got all of them to work for a different number of fields in the real problem, then I have to salute your perspicacity.

Brad
pelampeGIS Analyst

Author

Commented:
Yeah, that part was not difficult, strangely enough.

Luckily because I knew there were a certain number of characters that we were honing in on, I was able do discern a pattern with which to make the necessary changes.

I also did a little research prior to posting the initial question in an attempt to do it myself, and while I could some of the simpler applications, this was way too complex for me - hence that's how I got you all involved.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial