formula to put an asterisk in a column with split packing drinks description

[JEAN1972]

I have a worksheet with mixed food and beverages, my issue is put asterisk * in column I where description has splitting packages like 6x50cl, 6x75cl, 6x1,5L,,6x1 litre,.

This is a sample as there are beverages which has only e.g 1.5 litre or 0.75 cl and so on

Am looking a formula or vba to insert In column I an asterisk" * " .

The list is very bulky of 15000 rows of data.

Can anyone assist.

Have attached before and after results

[etaf]

based on your sample I tried this formula
=IFERROR(SEARCH("X1",A2,1),IFERROR(SEARCH("X2",A2,1),IFERROR(SEARCH("X3",A2,1),IFERROR(SEARCH("X5",A2,1),IFERROR(SEARCH("5c",A2,1),"")))))

BUT if there are hundreds of other possible sizes, this MAY not work

we can just add an IF to this to add the *


A better way - would be to create a List and use
=SUMPRODUCT(--ISNUMBER(SEARCH(listofthings,A2)))>0

attached both

[KOKOSEK]

I've put asterisk inside regexp pattern because on attached file is like: 80*6*33CL not 6x50cl

Please Login or Register  to view this content.

[JEAN1972]

Hello KOKPSEK

I have tried on a new sample of data starting at row 212 but indeed in applying your code did not showed the expected results .

It put * from row 212 to 364 (but row 2 to 211 gave the correct answer)which is incorrect in I have put column N which is correct output results.

Can you have a look what is going wrong.

[JEAN1972]

Bump bump bump

Can someone assist, thanks

[Bo_Ry]

Please try at I2

=IF(COUNT(SEARCH("X"&{0,1,2,3,4}+{0;5},A2)),"*","")

[FDibbins]

Bump bump bump

Can someone assist, thanks

Pleas wait at least 24 hours before bumping your thread - a little patience goes a long way here

[KOKOSEK]

Hello KOKPSEK

I have tried on a new sample of data starting at row 212 but indeed in applying your code did not showed the expected results .

It put * from row 212 to 364 (but row 2 to 211 gave the correct answer)which is incorrect in I have put column N which is correct output results.

Can you have a look what is going wrong.

Why:

2*6*70CL gives no asterisk

and

8*6*25CL or 20*6*33CL gives asterisk

[JEAN1972]

Why:

2*6*70CL gives no asterisk

and

8*6*25CL or 20*6*33CL gives asterisk

If you take in my last attachment xlsm column represent with * shows the correct answer but it has been done manually.

Using autofilter filter column N with * , you will notice in column A that all description has a packing details e.g 4X20cl, 6X25cl,6X20,

6X25CLBTL which are always at the end of the description


where if you filter column N with blanks

you will notice in column A that the description does not show it.

My goal was put the asterisk automatically without the need to do it manually.

[JEAN1972]

Please try at I2

=IF(COUNT(SEARCH("X"&{0,1,2,3,4}+{0;5},A2)),"*","")

Thanks for that formula solution