Formula Needed to Extract Middle Part of String

[DougStroud]

This is a separate formula request.
This related to a recent post, http://www.mrexcel.com/board2/viewtopic.php?p=1392308#1392308,
but different. The function of this one is to extract the middle string between the two colons, and remove the leading and trailing spaces.
I cannot get the middle string by itself.

I think maybe the MID function is a possibility, but I have failed to implement it properly.

=IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))<1,"",IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))=1,RIGHT($AB36,LEN($AB36)-FIND(" ",$AB36)-2),IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))=2,RIGHT($AB36,LEN($AB36)-FIND(" ",$AB36)-2))))

Guys : Beanies & Hats : Beanies
Here I need
Beanies & Hats

The second IF looks to see if there is one colon delimiter and returns the last value. The last IF looks to see if there are two colon delimiters, if this is true, then I need the middle string.
I am failing here. I recognize I am using the RIGHT Function, I am not able to write the MID function correctly, so I have left it out for example sake.

Thanks for any input/assistance.

 

[Norie]

Doug

Have you ever considered using UDFs for this?

For example you could easily extract that string using the Split function in a UDF.

 

[DougStroud]

Yes and no....
Why I am avoiding the UDF at the moment. I feel it crucial to learn how to write formulas to deal w/ such scenarios, the reason being is to develop my overall Excel skills and knowledge.
Secondly, I have the surrounding values being returned w/ formulae and like to maintain the consistency, for future edits and understanding of the way the values are derived.

The UDF is still beyond my grasp currently and would rather take these thing, meaning my learning approach in chunks. As you know, I am still working on VBA basics, so I am somewhat resistant to using a different method which opens up the door for other issues. If I were just looking to get an answer, that would be ok.... know what I mean?

But I am hoping to find an answer w/ the formulae and if that is not possible, then of course I am open to a different method.

 

[daniels012]

If D8 has my value,
This worked for me:

=MID(D8,FIND(":",D8)+1,(FIND(":",D8,FIND(":",D8)+1)-FIND(":",D8))-1)

Michael

 

[Norie]

Doug

I'm pretty sure there is a formula answer, but as you probably know I'm no formula guru.

If something like this came my way I'd immediately think of using code.

Perhaps the Split function as I suggested, or even Data>Text to columns...

Function GetMiddleBit(rng)
    x = Split(rng.Value, ":")
    GetMiddleBit = Trim(x(1))
End Function

 

[DougStroud]

Thanks Daniel!
This close... There are leading and trailing spaces.
I will muck around w/ this and see if I can find how to remove... maybe the Trim

 

[DougStroud]

Daniel-
Bravo- Thanks....
This got it....=IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))<1,"",IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))=1,
RIGHT($AB36,LEN($AB36)-FIND(" ",$AB36)-2),IF(LEN($AB36)-LEN(SUBSTITUTE($AB36,":",""))=2,TRIM(MID(AB36,FIND(":",AB36)+1,(FIND(":",AB36,FIND(":",AB36)+1)-FIND(":",AB36))-1)))))

 

[DougStroud]

No worries Norie....
I will hit you up when I get to UDFs, how is that? This stuff is like the future, I know its coming, not just sure when...

 

[jolivanes]

Here is another one (from http://www.ozgrid.com/forum/showthread.php?t=75574)
If the string is like
ABCDEF: GHIJKLMN: OPQRSTU
in cell B2, this will work:
=TRIM(LEFT(RIGHT(SUBSTITUTE(B2,",",REPT(" ",40)),80),40))

John

 

[jolivanes]

This should be a colon instead of a comma.
=TRIM(LEFT(RIGHT(SUBSTITUTE(B2,":",REPT(" ",40)),80),40))

John