Concatenation means to join together: consider the following

Inputs that we might want to join together
Duncan
Williamson
duncan williamson ltd
31 August 2012
100
3
19.12345679

Concatenating Words

Here are the results:

Duncan Williamson
duncan williamson ltd 41152
Duncan Williamson – duncan williamson ltd: 41152

Assuming that the first of the inputs are found in cell A4, this is how we did those things:

 =$A4&” “&$A5
 =$A6&” “&$A7
 =$A4&” “&$A5&” – “&$A6&”: “&$A7

alternatively, the same results achieved in a different but equivalent way:

 =CONCATENATE($A4,” “,$A5)
 =CONCATENATE($A6,” “,$A7)
 =CONCATENATE($A4,” “,$A5, ” – “,$A6,”: “,$A7)

Concatenation of words and values

If we concatenate words and fractions, this happens

Duncan’s daily rate of pay: 33.3333333333333

achieved by:

=$A4&”‘s daily rate of pay: “&$A9/$A10

Suppose we don’t want all of these decimal places …

Duncan’s daily rate of pay: 3333.33%
Duncan’s daily rate of pay: 33.33
Duncan’s daily rate of pay: 33

I used the =TEXT, =ROUND and =INT functions to solve the problem of the large number of decimal places

 =$A4&”‘s daily rate of pay: “&TEXT($A9/$A10,”0.00%”)
 =$A4&”‘s daily rate of pay: “&ROUND($A9/$A10,2)
 =$A4&”‘s daily rate of pay: “&INT($A9/$A10)

Two more examples:

duncan williamson ltd average number of invoices per day 19.12
Duncan’s reporting date: Aug 2012

here are my solutions of how to concatenate and format

 =$A6&” average number of invoices per day “&ROUND($A11,2)
 =$A4&”‘s reporting date: “&TEXT($A7,”mmm yyy”)

 

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: