I can't separate search and find a value, then use the value next to it minus the "^"

[kingpnp]

I would like a formula method along with a short macro to do it as well.

I know there's some sort of search/then Right formula thing going same within the macro but i still dont know how to keep the "^" out. see the attached excel file for an example. This main issue needs to be corrected for Column C cells. I also need a macro for Column D's formula. or a macro that would complete column C and D with the criteria listed within the Cells/Formulas


Two logics within Column C: (if value(A2) is located within B2, C2= next number in list( if 1, then C2= SPAY in its place)
SPAY - (if value(A3) is Not located within B3, then C3 = SPAY)

Formula within Column D is it a N or Y?
=IF(OR(C2="SPAY",C2=7),"N","Y")

Thanks for the assistance

[Fluff13]

How about a bit of both

Please Login or Register  to view this content.

Put this in a regular module.
the in C2 put
=Spay(A2,B2)

[kingpnp]

How about a bit of both

Please Login or Register  to view this content.

Put this in a regular module.
the in C2 put
=Spay(A2,B2)

Thanks Fluff. I tried it and its not working. Could you place the VBA into the attached excel Trial file. then save the attachment for me to download?

[Fluff13]

Here you go

[kingpnp]

Short and Sweet. Thank You Fluff.

[Fluff13]

Glad to help & thanks for the feedback