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?

 

[Aladin Akyurek]

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.

Control+shift+enter, not just enter:

=IF(COUNT(B2:B5),"Buy ","")&IF(COUNT(B2:B5)>1,SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISNUMBER(B2:B5),A2:A5&" "&B2:B5,"")),", ",", and ",COUNT(B2:B5)-1),LOOKUP(9.99999999999999E+307,B2:B5,A2:A5)&" "&LOOKUP(9.99999999999999E+307,B2:B5))

 

[amautaexcel]

Control+shift+enter, not just enter:

=IF(COUNT(B2:B5),"Buy ","")&IF(COUNT(B2:B5)>1,SUBSTITUTE(TEXTJOIN(", ",TRUE,IF(ISNUMBER(B2:B5),A2:A5&" "&B2:B5,"")),", ",", and ",COUNT(B2:B5)-1),LOOKUP(9.99999999999999E+307,B2:B5,A2:A5)&" "&LOOKUP(9.99999999999999E+307,B2:B5))

I am having a system limitation. The end user of this spreadsheet does not have Office365, so they won’t have TextJoin as an option. Any alternatives? Thank you very much.

 

[johnmpl]

Hi!

You can try this - only put the first formula and drag it down -. Blessings!

Note: If you erase A5:B5 or add some registers... you can see the changes of the formula. The last cell (in this case, C5) show you the desire result. Blessings!

Excel Workbook
	A	B	C
1	Fruit	Cost	*
2	Apples	$ 5	Buy Apples $5
3	Oranges	$ 1	Buy Apples $5, Oranges $1
4	Pears	$ 3	Buy Apples $5, Oranges $1, Pears $3
5	Bananas	$ 2	Buy Apples $5, Oranges $1, Pears $3 And Bananas $2
6	*	*	*
Hoja1

 

[amautaexcel]

Hi!

You can try this - only put the first formula and drag it down -. Blessings!

Note: If you erase A5:B5 or add some registers... you can see the changes of the formula. The last cell (in this case, C5) show you the desire result. Blessings!

Hoja1

*	A	B	C
1	Fruit	Cost	*
2	Apples	$ 5	Buy Apples $5
3	Oranges	$ 1	Buy Apples $5, Oranges $1
4	Pears	$ 3	Buy Apples $5, Oranges $1, Pears $3
5	Bananas	$ 2	Buy Apples $5, Oranges $1, Pears $3 And Bananas $2
6	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:74px;"><col style="width:332px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(A2="","",IF(COUNTIF(A$2:A2,"<>")=1,"Buy ",C1&IF(COUNTA(A:A)-1=COUNTIF(A$2:A2,"<>")," And ",", "))&A2&TEXT(B2," $0")) C3 =IF(A3="","",IF(COUNTIF(A$2:A3,"<>")=1,"Buy ",C2&IF(COUNTA(A:A)-1=COUNTIF(A$2:A3,"<>")," And ",", "))&A3&TEXT(B3," $0")) C4 =IF(A4="","",IF(COUNTIF(A$2:A4,"<>")=1,"Buy ",C3&IF(COUNTA(A:A)-1=COUNTIF(A$2:A4,"<>")," And ",", "))&A4&TEXT(B4," $0")) C5 =IF(A5="","",IF(COUNTIF(A$2:A5,"<>")=1,"Buy ",C4&IF(COUNTA(A:A)-1=COUNTIF(A$2:A5,"<>")," And ",", "))&A5&TEXT(B5," $0")) C6 =IF(A6="","",IF(COUNTIF(A$2:A6,"<>")=1,"Buy ",C5&IF(COUNTA(A:A)-1=COUNTIF(A$2:A6,"<>")," And ",", "))&A6&TEXT(B6," $0")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

John, thank you for your reply. I will give this a test and report back. I appreciate the forum's help. Thanks

 

[Aladin Akyurek]

I am having a system limitation. The end user of this spreadsheet does not have Office365, so they won’t have TextJoin as an option. Any alternatives? Thank you very much.

First, add the following code as a module to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now control+shift+enter, not just enter:

=IF(COUNT(B2:B5),"Buy ","")&IF(COUNT(B2:B5)>1,REPLACE(SUBSTITUTE(aconcat(IF(ISNUMBER(B2:B5),", "&A2:A5&" "&B2:B5,"")),", ",", and ",COUNT(B2:B5)),1,2,""),LOOKUP(9.99999999999999E+307,B2:B5,A2:A5)&" "&LOOKUP(9.99999999999999E+307,B2:B5))

 

[johnmpl]

Hi again!

If you want and UDF Solution, you can use this code in a module (Alt + F11 - Insert Module - Paste the code here):

Function Conc$(Rng1 As Range, Rng2 As Range)
    Dim i&, a$, sep$
    
    If Rng1.Columns.Count > 1 Or Rng2.Columns.Count > 1 Then Exit Function
    
    For i = 1 To Rng1.Rows.Count
        If i = Rng1.Rows.Count - 1 Then sep = " and " Else sep = ", "
        a = a & Rng1.Cells(i) & Format(Rng2.Cells(i), " $0") & sep
    Next i
    
    Conc = Left(a, Len(a) - 2)
End Function

And later, use the Conc function like the example. Blessings!

Hoja1

*	A	B	C	D	E
1	Fruit	Cost	*	Result	Buy Apples $5, Oranges $1, Pears $3 and Bananas $2
2	Apples	$ 5	*	*	*
3	Oranges	$ 1	*	*	*
4	Pears	$ 3	*	*	*
5	Bananas	$ 2	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:81px;"><col style="width:43px;"><col style="width:80px;"><col style="width:49px;"><col style="width:337px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 ="Buy "&Conc(A2:A5,B2:B5) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4