When we touch on the subject of spreadsheets in excel, many people have an idea formed that is very complex. Because of this we seek to explain and simplify some functions: TEXT, CONCATENATE AND &.
Through spreadsheet templates, we will help you put together business spreadsheets with these functions, using them as management tools for your business. Come on!
1 - TEXT FUNCTION
By using the TEXT function in your excel spreadsheet, you can do the conversion of any numeric value into a text, as well as assign a formatting.
- value: this is any numeric value or refers to any existing numeric value in a cell in your excel sheet.
- text_format: this is the numeric value represented by text that is surrounded by quotation marks.
The excel worksheet below seeks to elucidate each vendor's average weekly sales value. We use the TEXT FUNCTION to represent the numerical value to the format of $ 0,00.
= TEXT (G2; "R $ 0,00")
1 Step: Assign Value.
In cell B11, we first assign the numeric value that refers to the value represented in cell G2: = TEXT (G2;.
2 Step: Assign Text Formatting:
After, we insert the text format that will represent the value in Reais: = TEXT (G2;"R $ 0,00"). Press ENTER and you're done. The B11 cell will display R$ 840,00.
2 - FUNCTION CONCATENATE:
Using the CONCATENATE function in your excel spreadsheet, you can group numerous sequences of characters into a single text string.
- texto1: refers to the first item to be concatenated and obviously is mandatory.
- text2: Optionally you can add texts, which must be separated by a semicolon.
Example: In the cell A17 we try to describe in a sentence form the weekly average sales of the employee Carlos.
= CONCATENATE (A2; "REACHED"; B11; "IN THE WEEKLY AVERAGE")
Step 1: Select the A2 Cell that represents the employee's name Carlos as TEXTO1: = CONCATENATE (A2;
Step 2: We added the word REACHED as TEXTO2, between quotation marks and with spaces between quotation marks: = CONCATENATE (A2;"ACHIEVED";
Step 3: We add the cell B11 as TEXTO3, which represents the value of the average values of the weekly sales of the employee Carlos. = CONCATENATE (A2; "REACHED";B11
Step 4: As TEXTO4 we put the words IN THE WEEKLY AVERAGE, to complete the phrase, between quotation marks and with space between quotation marks: = CONCATERNATE (A2; "REACHED"; B11; "IN THE WEEKLY AVERAGE"). Press ENTER and you're done. The A17 cell will display the phrase CARLOS REACHED R $ 840,00 IN THE WEEKLY AVERAGE.
3 - &:
Through the operator & you will achieve the same result as the CONCATENATE function. It is recommended for shorter formulas.
In the present example, using & we reach the same result of the CONCATENAR function used for the employee Carlos.
= A3 & "REACHED" & B12 & "IN WEEKLY AVERAGE"
The difference is that now you will not have to type = CONCATENATE.
Just enter = and after the cell A3 that refers to the name of the official CARLOS and in sequence type &.
After quotation marks and with space between quotation marks enter REACHED.
Then type & and select the cell B12 that refers to the value of the average value of the sales week of the employee Mario. Type it &.
Finally, again between quotation marks and with space between quotation marks, type IN WEEKLY AVERAGE and click ENTER. The A18 cell will display the phrase MÁRIO REACHED R $ 725,00,00 IN THE WEEKLY AVERAGE.
Through the above explanations, we hope to have helped you to understand a little more about the TEXT, CONCATENATE and & functions for greater use in your excel spreadsheets in order to facilitate your work through management tools to business spreadsheets.