+ Reply to Thread
Results 1 to 5 of 5

How to create a sentence from variables in different cells - out of my depth!!

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    2

    Lightbulb How to create a sentence from variables in different cells - out of my depth!!

    Hi all - if possible, could you please take a second to help me?! I'm now well out of my depth!

    I'm trying to create a roster template that automatically generates a sentence which takes information from a selection of cells BUT omits certain phrases and includes variable punctuality to create a proper sentence. I have attached (i think...!) my roster in it's current state where i have been able to generate a list separated by commas, but have a couple of hurdles i cannot overcome alone! a LOT of trial and error to get the current formula working but have encountered some issues...

    At present my formula takes the day (i.e. Mon) from one cell, the shift (i.e. 6am) from another cell and presents as "Mon 6am, Tues 6am" etc. I have created the formula so that a full stop appears after the Saturday shift - however, there will not always be a saturday shift which opened a whole new can of worms for me - is it possible to create a formula that will change the punctuation depending on the variables i.e., shift? For example, i would LOVE it to take a Mon, Tues, Wed, Fri roster and display as: Mon 6am, Tues 6am, Wed 6am & Fri 6am.

    Obviously there would have to be a rule that recognised how many days/shifts there were and allocated the correct punctuation. I have googled the heck out of it but turned up nothing. Not sure if this is even possible.

    Secondly, i need to display the following terms in the matrix (notes for me about a certain person's availability for work on a certain day) BUT with my current formula these terms are carried across into the roster sentence (which i will be smsing to my staff) - and i dont want them too. For example, if i say "day only" on Monday it carries across to the roster as "Mon day only, Tues 6am" etc

    Sorry for the lengthy post - just wanting to get as much explanation of what i'd like down as possible to see if anyone can help me at all! Thanks for taking the time to read...!

    To summarise i need my formula (I have included this in the attachment) to do the following:
    [*] create a list of variable days/shift times: DAY <space> TIME <comma> etc...
    [*] omit the following terms: "NA, day only, arvo only, night only, day/arvo, arvo/night" from the roster sentence
    [*] create a list with variable punctuality (i.e., commas between each day/shift and then the last shift should be: "&" <space> DAY <space> TIME <fullstop>

    Can you help?? Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to create a sentence from variables in different cells - out of my depth!!

    This monster formula does most of what you are asking for:

    =SUBSTITUTE(IF(C7="","",IF(ISNUMBER(MATCH(C7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",C$4&" "&C7&", "))&IF(D7="","",IF(ISNUMBER(MATCH(D7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",D$4&" "&D7&", "))&IF(E7="","",IF(ISNUMBER(MATCH(E7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",E$4&" "&E7&", "))&IF(F7="","",IF(ISNUMBER(MATCH(F7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",F$4&" "&F7&", "))&IF(G7="","",IF(ISNUMBER(MATCH(G7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",G$4&" "&G7&", "))&IF(H7="","",IF(ISNUMBER(MATCH(H7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",H$4&" "&H7&", "))&IF(I7="","",IF(ISNUMBER(MATCH(I7,{"NA","day only","arvo only","night only","day/arvo","arvo/night"},0)),"",I$4&" "&I7&", "))&".",", .",".")

    It doesn't put the ampersand in before the last term.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to create a sentence from variables in different cells - out of my depth!!

    Further to this, I put that monster formula in P7 for testing purposes. You can now put this formula in K7:

    =IFERROR(REPLACE(P7,FIND(", ",P7,LEN(P7)-12),1," &"),P7)

    and it will substitute the last comma with ampersand. Copy both formulae down as far as you need to.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    01-28-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to create a sentence from variables in different cells - out of my depth!!

    Pete_UK!!!! I cannot thank you enough - thats is absolutely SPOT on! Thank you thank you!! :D

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to create a sentence from variables in different cells - out of my depth!!

    Glad it worked for you.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] Replace cells and create dummy variables
    By slacknoise in forum Excel General
    Replies: 7
    Last Post: 06-18-2012, 02:40 PM
  2. [SOLVED] Create Sentence using other cells
    By blacky1 in forum Excel General
    Replies: 3
    Last Post: 04-20-2012, 10:01 PM
  3. Excel 2007 : create a sentence from 4 cells
    By NavNeet2912 in forum Excel General
    Replies: 2
    Last Post: 04-22-2011, 11:03 AM
  4. How do i add a cell value in a sentence without using 2 cells?
    By Jared in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2006, 11:15 AM
  5. Combining text and cell value to create a sentence
    By StephanieH in forum Excel General
    Replies: 4
    Last Post: 06-13-2005, 09:05 PM

Tags for this Thread

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