Simplifying the TEXT, CONCATENATE, and Excel Function

69
36152
By simplifying the TEXT function, CONCATENATE and &
Excel Spreadsheets

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.

Excel Spreadsheets

Syntax:

  • 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.

By simplifying the TEXT function, CONCATENATE and &

Example:

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")

Consider:

By simplifying the TEXT function, CONCATENATE and &

1 Step: Assign Value.

By simplifying the TEXT function, CONCATENATE and &

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:

By simplifying the TEXT function, CONCATENATE and &

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.

Syntax:

  • 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.

By simplifying the TEXT function, CONCATENATE and &

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")

By simplifying the TEXT function, CONCATENATE and &

Step 1: Select the A2 Cell that represents the employee's name Carlos as TEXTO1: = CONCATENATE (A2;

By simplifying the TEXT function, CONCATENATE and &

Step 2: We added the word REACHED as TEXTO2, between quotation marks and with spaces between quotation marks: = CONCATENATE (A2;"ACHIEVED";

By simplifying the TEXT function, CONCATENATE and &

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

By simplifying the TEXT function, CONCATENATE and &

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"

By simplifying the TEXT function, CONCATENATE and &

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.

By simplifying the TEXT function, CONCATENATE and &

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.

Excel Spreadsheets

69 COMMENTS

  1. Hi Bruno, as PF always appears at the end of the text, you could use the RIGHT function to get the last 2 characters and use an SE to see if it's pf or not

  2. Good morning Rafael, I have a doubt, I do not know if it is possible, I searched several sites and do not find the solution. I need a form that does the following:
    column 1 store please column 2 150,00
    column 1 house column 2 200,00
    column 1 purchases please column 2 500,00
    if the string “pf” is in the text of column 1, then add the value of column two, in which case 650,00 will result. I already used the formula = SOMASE (E108: E122; ”PF”; F108: F122) but only sum if it has only two characters, maybe I need the formula to read the text to find the sequence pf then return the sum. since already very much

  3. It is possible, but as by the example you put the variations are many, it can be complicated. In general you need to understand:
    1 - the size (characters) of the entire address
    2 - the size (characters) of the number
    3 - use the CONCATENATE, RIGHT, and LEFT function to merge the terms correctly

  4. Good afternoon
    I have a column with several addresses R RIBEIROLES 25 / AV RIO BRANCO 223 etc ... Is it possible to insert a formula to insert a comma before the numbering?

  5. Hi Carlos, you can make a table with two columns, one with the name and another with the registry. Then just use PROCV to enter the name of a client (I recommend the use of list), show the tokenshop

  6. Hi Rafael;
    Please, can I get a tip for the problem below?
    I have an X number of clients, which have registered in different registries. It is important to associate the client with the name of the notary, so that when the client is entered in a cell, in another the name of the notary appears. I tried conditional and concatenation, but I did not succeed.
    Thank you
    Carlos DF

  7. Hi Terezinha, to CONCATENAR you can put as many fields as you want. For example = CONCATENATE ("="; A1; ","; ";"; "; A2;", ";"; ";"; A3;

  8. Hi Luiz, I usually solve this manually by putting "000000" & "A1", which would result in 000000123, assuming that 123 is written in A1

  9. Hi, I need help.
    I need to concatenate several numbers of OS of a column, leaving all in a single cell with the = sign in front and separated by commas, to be able to transfer to the maximum system because in the field of the system it only recognizes so;
    the column looks like this:
    235878
    235879
    235880
    235881
    I need to leave it in the cell like this:
    = 235878, = 235879, = 235880, = 235881,
    When I go to conctenar I can only do in cell A1 and A2, I can not drag to the others.

  10. Hi Luiz, this part of concatenation is manually same. In CONCATENAR you can put another text with the amount of 0 you need (if it is uniform) if you have variations, you will have to use an SE or something similar to get the desired result

  11. I have a cell that is formatted as '000000000' (there in cell formatting, custom type), when I fill that cell with 123 for example, it looks like this "000000123" "At the time of concatenating it, it only sees '123'.

    How do you think I can solve this?

  12. Hi Luis, maybe you need to create a routine for VBA so that you can join each of those lines with the concatenate. Direct in Excel I can not think of an automatic way.

  13. Good afternoon,
    How to use the concatenate function for multiple columns.
    Example: I have a series of numbers in the form below:
    FHTT09230C80
    FHTT09230C88
    FHTT09230C90
    FHTT09230C98
    FHTT09230CA0
    FHTT09230CA8
    FHTT09230CB0

    Notice that the FHTT0923 start repeats itself while the final 4 does not. I used the extract number function and created two columns. One with FHTT0923 (prefix) and the other with the 4 final digits (suffixes), and instead of repeating the prefix I would like to separate by commas. Like this,

    FHTT09230C80, 0C88, 0C90 ... and so on.

    This sequence has 3.210 numeric rows separated into two columns (prefix and suffix). How to merge this comma-separated data.

  14. Hi Osny, just use the CONCATENAR. But when you put the date, it loses its formatting, so you'll have to use the YEAR, MONTH and DAY formulas to restore the formatting forcefully. Assuming the first date in A1 would look something like = CONCATENATE (DAY (A1); "/"; MONTH (A1); "/"; YEAR (A1); "a"; etc.

  15. Hi Rafael, all right ???
    I need to put text with date:
    Ex: Period from 01 / 01 / 1900 to 01 / 01 / 1900
    each date being in a cell.

  16. Hi Vinícius, I understand, but it will be hard work, but the idea is the same, it would be something like
    =EXT.TEXTO(A1;1;7)&”-“&EXT.TEXTO(A1;7;2)&”.”&EXT.TEXTO(A1;9;4)&”.” etc

  17. Hi Rafa, how are you?
    Dude, I have a question regarding the "&" in the EXT.TEXTO formula.
    I want to leave the process number like this: "0055685-01.2013.4.03.6100"
    But I took the dash and the points to apply the formula, made some parts but could not go forward, could you help me? At the moment it is like this:

    00556850120074036100 = EXT.TEXT (A1; 1; 7) & "-" ...........................

    My question is not knowing how to close this gap and continue the formula, you know? I think you will be able to analyze and get my doubt, the doubt is that I can not proceed after this: & "-"
    Can you help me? I will be very grateful. Hug!

  18. Hi Marina, you can create an extra column that has a formula related to that column where you write pg. It could be something like = SE (A1 = pg; "PAY", "NOT PAY")

  19. Hello, how are you?
    I was able to use the conditional formula to leave the green "PAYMENT" and "I DO NOT PAY" red, but I'm trying to make my life easier hahahah; every time I put "pg" it replaced with "PAY" but I can not find a formula, I would have to use every time that "Find and Replace" option in the right corner. I have already tried the TEXT - REPLACE and CHANGE function, but I can not too, help me hahaha

    Thank you for your attention and for the stories.

  20. Hello Rafael, good morning!
    I need to supply a category column but I do not know which formula to use.

    Category Collaborator
    BICYCLES Beatriz
    ELETROPORTATEIS Jennifer
    Do you have any formula that takes this category and fills the name of the collaborator ??

    Since now, grateful . Gabriele

  21. I could not understand what you need exactly with your example, but to use SEERRO with CONCATENAR is quite simple, just do something like this:
    = SEERRO (CONCATENATE (A1; A2; A3; A4); "error response")

  22. Good Morning!
    I need to use the seer and concatenate it in a sentence, I do not know how to do it
    Alguém ajudar pinch me?
    PHRASE The dog of Olavo has by the white
    Sub for Puppy
    Sub dog by Cat Cat
    Altered sentence

    PHRASE The dog of Olavo has by the white
    Sub by Canary
    Sub dog by Cat Cat
    Altered sentence

  23. Hi Lourival, I did not quite understand what you need not to. If you can explain it in a different way, maybe I can help

  24. Good afternoon! In a family game, a person is drawn with the name of a team, who can be the principal or visitor. When client becomes easy, the problem is when the person drawn receives a visitor, because I want to index the result, without having to be typing. When participants are few is not labor intensive, the problem is when they are over 20. Ex: Luiz was drawn with Santos that plays with Vasco, I made a spreadsheet with the results and I look for the cell that Santos is in, okay. Now I was drawn with Vasco, How do I make the general spreadsheet look for the visitor in the case of Vasco and put it on the line below in the case of Santos, or in the same column, since we consider the winning teams to be successful, regardless of whether they are in the first as in the second column. I actually want excel to find the name "Vasco", and play in a specific column. I hope I understand. Thankful. Lourival Avanzi

  25. Hi Sonia, just use the formula = SE (A1 = 100; "Moved" & today (); "Study outside" & today ())
    I only considered the two conditions you mentioned, but if you have more you can add more SEs and if the date now is a different value than today, just change the function TODAY in the formula

  26. Someone help me?

    I need to use SE and CONCATENATE.

    THE CASE IS THE FOLLOWING.

    if a1 = 100, concatenates text "Moved with date now", if a1 = 50, concatenates text "Study out with date now"

  27. Hello!
    How do I import data from one worksheet to another?

    Example of my spreadsheets (1 worksheet = Books / Sheet Entry 2 = 2017-2018 Loans):

    1 Worksheet (Book Entry) = Tombo ---- Author ----- Book Name
    ------------- 01235-Assis, Ax of --- Dom Casmurro

    ______________ / / _______________ / / __________

    2 Spreadsheet (2017-2018 Loan) = Tombo --- Author ---- Book Name

    ______________ / / _______________ / / __________

    In the above models, I need to carry the data from the 1 worksheet to 2 worksheet.

    How do I do this when typing the 1 spreadsheet (01235) in the 2 worksheet, does it fill in the "Author" and "Book Name" fields automatically?

    Note that my spreadsheets are in "Calc", if you do not use this one, it could explain how to do it in excel itself, since many functions are practically identical.

    I thank you.

    Best regards:

    Alessandro Sousa

  28. Hi Eluir, it all depends on the formatting of the cells you have. In this case, I think you just have to concatenate and format the cells for text

  29. Good afternoon, instead of JOINING TEXT, you have to JOIN TEXT while keeping ZERO

    Eg

    I would like to join 01 02 03 04 05 and the result was 01020405 and would not like 12345

    Anybody know?

  30. Good morning, I'm a broker and would like to pass the Excel contacts files to the cell, ie concatenate to name column a and phone column b with comma to convert as would be the fomula

  31. Hi Americo, I did not quite understand what you meant. But if you have a formula, it will always pick up the defined fields. If you want to modify this, you will need to use macros or VBA code

  32. good evening. I'm new to working with excel formulas and macros inda I do not know how to do but at the moment I'm working quite a bit in excel and I have a table in which all values ​​are being changed manually since I can not use pv formulas I have to frequently erase the 2 balances fields and the rest of the formulas are interconnected with these cells and do not have the desired results.
    my doubt and if some simple or a little more complicated way to make a cell maintain the rally of an account even if one of the parcels is eleminated and replaced by another that in turn also wanted her to keep the result back the new value collapsed in the cell.
    I hope you understand what I am explaining and that you can help me.

    thank you

  33. Hi Elen, I could not think of any very clever way to do this process either. Maybe if you use the conditional SE to understand whether the value ends in zero or not (for example, SE (RIGHT (F1; 1) = 0; etc), you will be able to create a different CONCATENAR by putting the 0 value manually in the formula. Something like = SE (RIGHT (F1; 1) = 0; CONCATENATE (F1; 0; G1); CONCATENATE (F1; G1)

  34. Good Morning. I have column F with unit value and column G with that value in words. In column H I concatenated the previous 2 columns, however when I do this the value does not appear with 0 in the decimal place.
    Example:
    115,70 Unit Value
    In extenso: one hundred and fifteen reais and seventy cents
    Concatenado: 115,7 (one hundred and fifteen reais and seventy cents)

    I need it to show zero. Can you help me?

  35. The problem with you removing that piece of text and working on it is that Excel will not understand this value as an hour, but as text, making calculations impossible.

  36. Good morning, congratulations to the website. I have the 12-01-17 19: 43: 30 -0300 cedula and I want to remove the text -0300 and convert it to time format and returns the data to the spreadsheet that will be automatically fed. do I proceed to automate the removal of -0300, or do I incorporate the time difference?

  37. Hi, Fabio. I believe that in this case it is worth you to use the conditional SE to arrive at different forms of concatenation according to the given answers.

  38. Rafael, good morning!

    I'm having a formula on a spreadsheet, which is read-only. This worksheet fetches information from the other worksheet, where information is actually populated.
    The problem is in "concatenate", because I do not always have the "client replica, customer replica response, etc." (Phrases to start content from other cells)

    Wanted that if there was no filling of the customer response cell, the formula leave blank, rather than leave the concatenate phrase.

    For it is now presented as follows:

    No Contest: 4174
    PS-168 GAS SERVICES - PORTER 12X36 DAY 44 (J) - R $ 11819.64
    08/11/2017 10:58
    The employee NILxxx ROxxx xxxx appears absence of work on the 26 / 09 day, with medical certificate, without substitution.
    - Customer Response -
    - Customer Replication -
    - Customer Replica Response -
    - Customer rejoinder -
    - Response to Customer's Rejoinder -

    = Concatenate (IFERROR (VLOOKUP (A4: $ A $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 6, FALSE); ""); CHAR (10) "- Response to Customer -"; CHAR! (10); (IFERROR (VLOOKUP (A4: $ A $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 11, FALSE); "")); CHAR (10) "- Customer Replica - "; CHAR (10); (IFERROR (VLOOKUP (A4: $ A $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 12, FALSE);" ")); CHAR (10)" - Reply Customer Replica - "; CHAR (10); (IFERROR (VLOOKUP (A4: $ a $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 15, FALSE);" ")); CHAR (10 ) "- Customer Rejoinder -"; CHAR (10); (IFERROR (VLOOKUP (A4: $ A $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 16, FALSE) "!")); CHAR (10) "- Rejoinder Customer response -"; CHAR (10); (IFERROR (VLOOKUP (A4: $ a $ 10001; [COGEC.xlsx] Disputes $ A4: $ S23; 19, FALSE);! (I.e.

  39. The formula is a bit complex, but if the problem is when you have fewer codes, you can use the conditional SE to replicate the formula by changing the arguments that mention 3 codes.

  40. Olá a todos!
    I need a solution to this situation:
    In a cell (a1) I have this data: 212; 213; 214
    In another sheet I have database in column, with the number and name ex: 212 Carla; 213 Vitoria; 214 João
    I need to get the information to (a1) and know what people are available.

    I was able to solve the situation by:
    =PROCV(SEG.TEXTO(A1;1;3)*1;Nomes!U2:V79;2;FALSO)&”;”&PROCV(SEG.TEXTO(A1;6;3)*1;Nomes!U2:V79;2;FALSO)&”;”&PROCV(SEG.TEXTO(A1;10;4)*1;Nomes!U2:V79;2;FALSO)

    The problem is when the A1 cell instead of three codes has only one or two ex: 212; 213

    Thank you!
    Mario

  41. Hi Juliana, good morning!
    in the "General" field you have to select "DATA"
    This field to change is in "INITIAL ABA" ...

  42. excellent explanation! Loved it and it helped me a lot! 1.000 note

  43. Hi Juliana, the date appears so because Excel recognizes as dates as a number in time counted from 01 / 01 / 1900 if I am not mistaken. So the date 20 / 10 / 2014 has 41.932 days, understand?

    When using the CONCATENATE function, it pulls this value and therefore its confusion. To correct this, you can use the functions, DAY, MONTH, and YEAR as follows (assume the first date is in cell A1 and the second in cell A2.

    =CONCATENAR(DIA(A1);”/”;MÊS(A1);”/”;ANO(A1);” – “;DIA(A2);”/”;MÊS(A2);”/”;ANO(A2))

  44. Good morning,
    I would like to pull a date from each cedula and put them together in a single cell phone.
    Ex. A1 (20 / 10 / 2014); A2 (27 / 10 / 2014); A3 (20 / 10 / 2014 - 27 / 10 / 2014), but when I put the formula the date appears as follows: A3 (41932 41939). can anybody help me? Thankful!!

  45. Hi, Marcia, how are you?

    There are some ways to do this. I'll list here:

    1 - You can create a macro and simply push a button to copy only the values ​​that have LENDO and copy / paste in the LENDO tab. For this it would be necessary to create a simple step by step with variable references

    2 - in the LENDO tab you can create a dynamic list according to the values ​​of the tab I WANT TO READ, it would be more or less like this example here - https://blog.luz.vc/excel/variacoes-da-funcao-procv-no-excel-mais-de-uma-condicao-e-valores-repetidos/

    3 - A third simpler option is to use filters on the I want to read tab to see only the values ​​of who is reading

  46. I would do an auxiliary table with the numbers in one column and the corresponding months in another. Then you would use a PROCV to fetch the value in the months column according to the number entered

  47. I have a table here and I need some help. When you type 1 in the cell it converts to text, in my case it would be a month. EX: If you type 1 in the cell it shows the month APR. How to do?

  48. You can use the CONNECT function with the LEFT, RIGHT and NUMBER functions.

    That way you count the number of characters and put in the function CONCATENATE the left function always with the value 2, add the comma and put in the right the number of characters minus 2.

  49. I have a list of integers, and I would like to insert a (comma) after the second digit of this list, how do I?

  50. Good Morning. I would like to do the following: I have a list of column names with a numeric value in the front column (example: Carlos in A1 and 3 value in B1) and I would like that when I choose the name "Carlos" from a drop down list the "3" value in the front column of the drop down list (how confusing!). Has as?

  51. Hi Bruno, the error occurs because its result is a text and not a formatted number to be viewed as date and Excel can not read this type of data in formulas that need numbers.

    You can create an extra column with the DATA function searching for these values ​​and then averaging based on those values, which will be dates

  52. Hello, how can I do the following situation:
    I want to calculate the average time of a company's employees.
    I made one cell counting the years (X) and another counting the months (y) for each one.
    I concatenated the results (XY) and set the TEXT function to display "X Years and Y-Months".
    How can I average these individual times? When I average and select the values ​​it is not possible, possibly because they are concatenated.
    Is there an easier situation?

  53. It's 100% possible Brandon, but you need to understand what data you have. For example:

    1 - If you have a table with company data and percentages and want to do this in a new part, just use PROCV

    2 - if you have general values ​​for each company and want to know the percentage of the total, is also simple, just do the company's value (which can be obtained with VLOOKUP) is divided by the total

    I think that's it, if you have more questions, just talk

  54. OLA NEEDS AN FORMULA WHERE I PLACE THE NAME OF THE COMPANY AND AUTOMATICALLY OF THE PERCENTAGE IN THE COLUMN ON THE SIDE. EXAMPLE: "COMPANY" = 3%. HAVE THE POSSIBILITY?

  55. Hi Ale,

    in this case you need to use the SE function within SE. It would look something like = SE (A1 = 10%; "New Client"; SE (A1 = 25%; "Proposal Delivers" ...

    and so on. Hope this helps

  56. How does a table return a text in the Status column depending on the percentage of the% column?
    % | Status
    10% | new customer
    25% | submitted proposal

  57. Hi Philip, how are you?

    Have you tried in the concatenate function to directly fetch information from the other worksheet?

    Anyway, I was left with a bit of doubt as to which error is appearing, can you give a somewhat more detailed explanation of what appears wrong?

  58. When I need to use data from another worksheet, I make the following command: = load_name! Cell_name. The data is accessed perfectly.
    But I need the file name comes from a specific cell, so use the concatenate this way = concatenate ( "="; A1; "cell_name!"). The problem is that the result of this CONCATENAR is a text and not the formula to access the external data. Can you help me?

LEAVE AN ANSWER

Please, write your comment
Please enter your name