If cell has pack or pk return Pack of 100 or pk100

[Jaffabfc]

Hi,

I am wanting a formula for this spreadsheet.

Column A has a list of descriptions for example "red pens pack of 100" or "red pens pk25"

What i am wanting is in column B to pick up Pack of 100 or pk25 (numbers would change).

Ive got partly there with this:

=IF(OR(ISNUMBER(SEARCH({"pack","pk"},A2))),"pack","pk")

but cant get any further

 

[Jaffabfc]

I also would need a formula for in column c to say that is the cell a2 includes a colour put the colour in cell c2, then cell c3 for a3 etc.

so the colours would be every colour pink/red/green/black/violet etc

 

[Jaffabfc]

is this a little tricky for everyone? or is it impossible lol

 

[Smithsat34]

It isn't clear from your question what you want the content of column B to actually be, your formula returns "pk" on a blank cell, and "pack" on cells that contain either "pack" or "pk".
I'm guessing from your second question you want to extract the colour value from cell A2, how are you defining 'every colour' is there a separate listing of the colours used.
Ultimately do you want the number in each pack by colour, but what use is that on a row by itself. Any reason you can't use the auto filter?

Happy to give this a go with a bit more direction...

bob

 

[Jaffabfc]

Hi Sorry never been good at explaining.

basically:

A2= RED PENS PACK OF 100
A3= PK10 GREEN BALLS

B2= PACK OF 100 (if formula was successful)
B3= PK10 (if formula was successful)

C2= RED (if formula was successful)
C3= GREEN (if formula was successful)

there is no list of colours because the list would be huge, although i could just get a huge list off the internet and stick into a seperate sheet to link to, if this helps.

thanks

 

[Jaffabfc]

Ive done a work around for the colour, but its not ideal.

I have put in J1:X1 some colours then in J2:X61401 a formula of "=IFERROR(IF(SEARCH(H$1,$a2,1),H$1),"")" then concartenated in another cell to put all colours toghether.

The pack side i am finding an issue as i can get it to say if there is a pack but cant get the numbers after.