+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,046

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

    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
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,743

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

    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
    Attached Files Attached Files
    Last edited by etaf; 03-24-2019 at 05:57 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,200

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

    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.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  4. #4
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,046

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

    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.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,046

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

    Bump bump bump

    Can someone assist, thanks

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,096

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

    Please try at I2

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

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,298

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

    Quote Originally Posted by JEAN1972 View Post
    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,200

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

    Quote Originally Posted by JEAN1972 View Post
    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

  9. #9
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,046

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

    Quote Originally Posted by KOKOSEK View Post
    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.

  10. #10
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,046

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

    Quote Originally Posted by Bo_Ry View Post
    Please try at I2

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] formula or vba to trim clean description column from specific wording
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2018, 04:46 AM
  2. [SOLVED] formula to extract unique data and adding drinks pack size description only
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2017, 03:34 PM
  3. Numbers before 1st asterisk, 2nd asterisk, 3rd asterisk
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 08-23-2016, 12:57 AM
  4. Replies: 4
    Last Post: 02-27-2015, 04:22 PM
  5. Maximum drinks for minimum ingredients.
    By fixit9660 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-19-2013, 03:56 PM
  6. Replies: 5
    Last Post: 07-06-2012, 03:21 PM
  7. Split Description into 3 Columns
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-10-2009, 05:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1