Generate list of text values and include "and" when it's the last value.

[amautaexcel]

Dear Excel Forum,

I am stumped by the following problem. I hope one of you can help me.
Goal: Create a sentence that lists items and it inserts the word "and" for the last entry.

I have a table where the user inputs the prices. If the price is included the items and the price is included in a sentence in a different sheet.

Apples . $5
Oranges $1
Pears $3
Bananas . $2

So for the table above, it would be: "Buy Apples $5, Oranges $1, Pears $3 and Bananas $2"

If the table only had apples and pears, it would be "Buy Apples $5 and Pears $3"
If the table only had pears and bananas it would be "Buy Pears $3 and Bananas $2"

I can string the text with if statements, but I have not been able to figure out how to tell it to include the "and" for the last entry.

Any thoughts?

 

[FDibbins]

Do a count on the spaces, then substitute the last space for spaceANDspace

 

[amautaexcel]

Do a count on the spaces, then substitute the last space for spaceANDspace

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 87"]
<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]Do you mind elaborating on this?[/TD]
[/TR]
[TR]
[TD]How can I replace just the last to " and "[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]
Vba?

Thank you very much.

 

[jammoca]

If you have Excel 2016, you could use the function ... TextJoin .... which is purpose-built to do exactly what you want.

However, if you don't have Excel 2016, I need to know a few things from you ...

* Is a user ever going to input more than 7 items .... remember, you can only have a maximum of 7 if statements in a formula
* Is the item and its corresponding price in the same cell, or 2 separate cells

Kind regards,

Chris

 

[Aladin Akyurek]

Are the fruits and prices in a 2-colum table or just in one column?

 

[FDibbins]

x-posted here
https://www.excelforum.com/excel-fo...commas-and-insert-and-for-the-last-entry.html

If your sample is anything to go by, your data is very messy. Some entries have . in them, others done, some have 2 spaces, others not and some have a space at the end, again, others not.

 

[amautaexcel]

My apologies. It's supposed to be a table, 2 columns.

Fruit Cost
Apples $5
Oranges $1
Pears $3
Bananas $2

The table has no prices and the user gets to put the prices in. Depending if the price is filled out a sentence would string the text together, so for the completed table it would be like this:

So for the table above, it would be: "Buy Apples $5, Oranges $1, Pears $3 and Bananas $2"

If the table only had apples, pears and bananas, it would be "Buy Apples $5, Pears $3 and Bananas $2"
If the table only had pears and bananas it would be "Buy Pears $3 and Bananas $2"

The problem that I am having is that I cannot tell it to put an "and" right before the last entry. Similarly, if there's only one entry, i could not say "Buy and Apples $5".

I wrestled with this all weekend and I am stuck.

Thank you for your help.

 

[amautaexcel]

I have excel 2016 and we are working with less that 7 items.The data is in a table of 2 columns.

 

[Aladin Akyurek]

I have excel 2016 and we are working with less that 7 items.The data is in a table of 2 columns.

If you have TEXTJOIN on your 2016 system...

Control+shift+enter, not just enter:

=IF(COUNT(B2:B5),"Buy ","")&TEXTJOIN(", ",TRUE,IF(ISNUMBER(B2:B5),A2:A5&" "&B2:B5,""))

 

[amautaexcel]

If you have TEXTJOIN on your 2016 system...

Control+shift+enter, not just enter:

=IF(COUNT(B2:B5),"Buy ","")&TEXTJOIN(", ",TRUE,IF(ISNUMBER(B2:B5),A2:A5&" "&B2:B5,""))

TextJoin does not account for the "and" before the last entry. Only seems to put commas and spaces. Am I missing something?

Again, the example would be, if they have Bananas and apples only, it should read "Buy Apples $$ and Bananas $$"

Thank you.