#### DougStroud

##### Well-known Member

- Joined
- Aug 16, 2005

- Messages
- 2,968

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.