Make sentence from data in cells in Excel

Question

I have a form with three cells like A1 - Apple, A2 - Banana and A3 - Oranges and the column B is where user inputs there data as how many each fruits they want

If the user fills the form as Apple = 2 Banana = 0 and Oranges =3

I want the code to output it as: "I want 2 apples and 3 oranges." With my code, I am having issues in placing "And" if user doesn't want all of these three fruits.

my formula is: ="I want "&B1&" "&A1 &", "&B2&" "&A2&" and "&B3&" "&A3 and output is "I want 2 Apple, 0 Banana and 3 Oranges". I want to get rid of the cell which doesn't have any order or Value

Answer 1

Actually I did a similar process· with a list· of countries . The user could select· the desired countries with a checkbox . Then a formula· would count· the number· of countries and string them together depending upon three· IF conditions: there was only one country· , there were only two· countries , and there were more than two· countries . The IF conditions governed where and whether an "and" was in the string.

You could use the same idea to count the instances of response for each fruit, i.e., if only 2 apples and 1 banana are wanted then there would be 2 instances.

Answer 2

You could use if

="I want "&IF(AND(B1 <>"",A1 <> ""),B1&" "&A1&",","") &IF(AND(B2 <>"",A2 <> ""),B2&" "&A2&",","")&" and "&IF(AND(B3 <>"",A3 <> ""),B3&" "&A3&",","")

Alternatively you could use a set of formulae where for each pair you write your if and then you string the results together. For instance if in column C line 1 you wrote

=IF(AND(B1 <>"",A1 <> ""),B1&" "&A1&",","")

then you could use the auto-fill handle (the black square on the bottom right of the selected cell) to populate the same formula for all the other rows (by clicking on the auto-fill handle and dragging down), and your column C entries would either show "N of FRUIT," or "" Then just string together all of your column C values:

="I want "&C1&C2&C3

I find the second approach less tiresome. Excel does the copying (and variable replacement).

Neither approach is smart enough to handle the case where the last entry is empty, but it's not even a proper scripting language so you have to live within its limits or really go overboard with the conditional syntax.