Mid Function not working

[buddhajb]

I'm using the following MID function: =MID(I9,SEARCH(" :",I9,1)+2,SEARCH(" :",I9,SEARCH(":",I9,1)+1)-SEARCH(": ",I9,1))

Cell I9 contains: "JES Access Manager 7 2005Q4 : Deployment Example 1: AM Load Balancer, Distributed Authentication, a

I just want to extract the following: Deployment Example 1

I'm getting the #Value! Error.

What's wrong with this formula?

[NBVC]

Try:

=MID(I9,SEARCH(":",I9,1)+2,SEARCH("^^",SUBSTITUTE(I9,":","^^",2),1)-SEARCH(":",I9,1)-2)

[Shijesh Kumar]

try this


Change "C12" to cell address where data is placed

Please Login or Register  to view this content.

[buddhajb]

Try:

=MID(I9,SEARCH(":",I9,1)+2,SEARCH("^^",SUBSTITUTE(I9,":","^^",2),1)-SEARCH(":",I9,1)-2)

Some of these do not have two colons, but only one colon. These instances return a #Value!. How would manipulate the above formula to not show #Value! but just a blank cell?

Thx!!!!

[NBVC]

Try:

=IF(LEN(I9)-LEN(SUBSTITUTE(I9,":",""))=2,MID(I9,SEARCH(":",I9,1)+2,SEARCH("^^",SUBSTITUTE(I9,":","^^",2),1)-SEARCH(":",I9,1)-2),"")