finding text between two /

[hg090589]

Hello,

I have a formula that finds text between the second / and third /

Hello/Waste Sludge Processing/Sludge Pre Conditioning/Sludge Thickening/Thickener Stream/PRIMARY SLUDGE THICKENER

so for this I have:
=TRIM(LEFT(SUBSTITUTE(MID(F6,FIND("|",SUBSTITUTE(F6,"/","|",2))+1,LEN(F6)),"/",REPT(" ",LEN(F6))),LEN(F6)))

Which is fine and displays : Sludge Pre Conditioning

But I have some text strings that are shorter and don't have / in them.is there a way to display those by adding something onto this formula to display ones like this that are shorter instead of it displaying Value?

Hello East SPS

 

[steve the fish]

See if this does it:

=IF(LEN(F6)>LEN(SUBSTITUTE(F6,"/",""))+1,TRIM(MID(SUBSTITUTE(F6,"/",REPT(" ",99)),99*2,99)),F6)

 

[hg090589]

See if this does it:

=IF(LEN(F6)>LEN(SUBSTITUTE(F6,"/",""))+1,TRIM(MID(SUBSTITUTE(F6,"/",REPT(" ",99)),99*2,99)),F6)

Thanks so much this works.

does the /quote just leave whatever is in F6 if the if statement is false?

Thanks again.

Helen

 

[steve the fish]

It checks to see there are 2 slashes in the target cell. If so it gets either whats between the 2nd and 3rd slashes or if only 2 whatever is after the 2nd slash. If less than 2 it just returns the cell value.