Find specific text error

[Pikey]

I have a formulae in an Excel cell that looks at a text string and returns a code dependent on the found text. I am having to a add a similar code to one already used and have an issue with the result. The formulae is :-

=IF(ISERROR(FIND("-SL",$C$4,1)),"","SMT LED")&IF(ISERROR(FIND("-CL",$C$4,1)),"","Conventional LED")&IF(ISERROR(FIND("-B",$C$4,1)),"","Bulb")&IF(ISERROR(FIND("-LED",$C$4,1)),"","SMT LED")&IF(ISERROR(FIND("-SLR",$C$4,1)),"","SMT-LD-RD")

The two embolden characters "-SL" & "-SLR" have the same firth three character and the formulae cannot separate them.

The text string is P725-M-7W2H-6T-26A-SLR-R-FC24-G-PU or P725-M-7W2H-6T-26A-SL-R-FC24-G-PU
Resulting code should be either SMT-LD-RD or SMT-LD-RD but I get SMT LEDSMT-LD-RD

Can someone please help as it's driving me mad!

Many Thanks

Pikey

 

[dUBBINS]

Off the cuff, if it won't differentiate, maybe try to make the first one:

"-SL-"

 

[Pikey]

Yeah, that was my suggestion, but the team have already used this in Quotes, BOM’s and route cards. Unfortunately I’m stuck with -SLR . Do you have any alternatives that may completely replace my formula but do the same job?

 

[jtakw]

Hi,

Your comment above, what does that have anything to do with adding the hyphen (i.e. from "-SL" to "-SL-") within the formula?

=IF(ISERROR(FIND("-SL-",$C$4,1)),"","SMT LED")&IF(ISERROR(FIND("-CL",$C$4,1)),"","Conventional LED")&IF(ISERROR(FIND("-B",$C$4,1)),"","Bulb")&IF(ISERROR(FIND("-LED",$C$4,1)),"","SMT LED")&IF(ISERROR(FIND("-SLR",$C$4,1)),"","SMT-LD-RD")

 

[Pikey]

Okay take,

I see where you’re coming from let me try that and see if it sorts it out.

 

[Pikey]

Hi Jtakw,

That worked!

Many thanks for your reply

Best Regards

Pikey