Microsoft Excel provides another set of operators to deal with the concatenation of text and combine ranges of Cells for calculations.
Concatenation Operator
This operator ONLY works on text data. It concatenates the text values or the values in the reference Cells to produce the concatenated text.
If you apply this with any other data type; that will convert to text and concatenate the two values. For example, if you give “1.2 & 2” the result shows as “1.22” which means, it converts both the values as text and concatenated the values.
Another example is if you concatenate “Apple & Banana”; the result shows “AppleBanana”.
As I mentioned, it converts any type to text; “TRUE & FALSE” produce “TRUEFALSE” results; these TRUE and FALSE are the Boolean functions.
Reference Operators
These operators are useful to select the ranges of Cells for calculations. These can work on any type of data for selection; basically, these are selection operators, and these will NOT work on any data; but when you use these in the Formulas; those formulas work on the selected data.
Range Operator (“:” – Colon sign )
The range operator is used to select the ranges of Cells for calculations.
For example, to select the range of Cells in A & B columns, from the 1st row to the 10th row we can use this operator like A1:B10. We can use this in calculations; for example, to sum the values in the range we use this operator in the formula as “=SUM(A1:B10)”.
Union Operator ( “,” – Comma sign )
Using the Range operator we can select, a range of consecutive Cells. How to select the range of non-consecutive Cells? Using the Union operator we can select multiple ranges of Cells and each range can be separated by a comma operator.
For example, to select the ranges of Cells in A & C columns to calculate the sum of the values, the formula like “=SUM(A1:A10, C1:C10)”.
Intersection Operator ( ” ” – Space sign )
This is an interesting operator; which selects only the ranges of Cells that are common in the selection. The ranges would be separated by a space. For example, to sum the Cells which are common in the ranges; “=SUM(A1:C5 C4:C10)”.
We discuss more Excel functions through upcoming Articles.
🙂 Sahida