Add numbers and text in one cell

[elsmith9035768]

See attached sheet. In E2, I am trying to combine the amount in column C with an abbreviation of the text in column B. Not sure how, and all I can find online is how to combine one amount with one abbreviation. I need them all in one cell. E2 shows the result I need. Thanks.

 

[AlanSidman]

Here is a messy formula for the exact example provided.

=CONCATENATE(C2&VLOOKUP(B2,Sheet2!$A$1:$B$3,2,0)&", ",Sheet1!C3&VLOOKUP(Sheet1!B3,Sheet2!$A$2:$B$2,2,0)&", ",Sheet1!C4&VLOOKUP(Sheet1!B4,Sheet2!$A$1:$B$3,2,0))

This assumes a lookup table on Sheet2 that look like this:
Apples ap
Grapes gp
Oranges og

 

[bosco_yip]

1] In helper D2, formula copy down :

=IF((C2<>"")*(TYPE(C2)=TYPE(C1)),D1&", "&C2&B2,C2&B2)

2] In Result E2, formula :

=INDEX(D:D,MATCH("*",D:D,-1))

Regards
Bosco

 

[elsmith9035768]

Thanks