Here we are going to use Query aggregation of text strings. valeur2: valeur à ajouter à valeur1. Create a CSV file. A mathematical expression that uses ranges of the same size. 1. The formula used is simply: =CONCATENATE (A2,”, “,B2,” “,LEFT (C2,1),”. So what remains is fetching all the other results into one cell. But this is obviously not a perfect solution in any way. 1. Step 3: Combine this named range with the INDIRECT function to refer to this string range inside your other formulas, which gives you a dynamic named range. BeispieleIn OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. 0. The TEXTJOIN Function Users of Excel 2019+ have access to the TEXTJOIN Function, which merges multiple strings of text together, separating each string with a specified delimineter. Menggabungkan teks dari beberapa string dan/atau array, dengan pembatas yang dapat ditentukan memisahkan teks yang berbeda. . When a range with both width and height greater than 1 is specified, cell values are appended across rows rather than down columns. To use CONCAT: Open your Google Sheet. Learn how to use CONCATENATE to combine cells in Google Sheets without losing data. The Query function in Google Sheets is the program’s most powerful and flexible feature. For information about using text expressions in templates, see Text expressions. The arrayformula goes down the rows of the spreadsheet and combines the data from the two columns. Tip: You. After that, enter an ampersand and specify a delimiter with double quotation marks. And that is how you can use the CONCAT and CONCATENATE functions in Excel and Google Sheets to combine words and build a. 1. ,Under the sheet absent in cell B2 paste that and copy it accordingly to the right: =CONCATENATE(B3:B100) Do exactly the same steps for the late sheet by replacing the absence to late and "a" to "l"How to Use TEXTJOIN Function in Google Sheets. The CONCAT function is a simpler function that does not offer as many options as the JOIN. Appends strings to one another. Append Text With the Ampersand Operator . My unique list looks like so:2. The CONCAT function is a powerful tool that allows you to combine the contents of two. How to Add Text With a Formula in Google Sheets Combine Text With the CONCATENATE Function. 1. Click the first cell that you want to refer to in the join and then press + (the plus sign). . How for uses CONCATENATE in Google Sheets. valeur1 et valeur2: ces entrées peuvent être des valeurs scalaires ou des références à des valeurs scalaires, y compris les types numériques et textuels. By merging multiple values into one, CONCATENATE streamlines data organization and presentation, making it an essential tool in financial analysis. CONCATENATE and CONCAT Functions Prior to Excel 2019, the only concatenate function available was the CONCATENATE Function. Google Sheets - Query - Concatenate text to returned data. Double-click on the cell or press F2 so that you are in edit mode in the cell. CONCATENATE: Appends strings to one another. Now click on the kind of merge you want from the options—Merge all, Merge horizontally and Merge vertically. The SELECT clause is the first clause that you start your queries with. Here we discuss four easy ways of concatenating strings with separators using the following dataset. Pusat Bantuan. In Google Sheets, the CONCATENATE function allows you to easily combine the values from two or more cells into a single, separate cell. Combine columns in Google Sheets using the TEXTJOIN function. I have one date field that has varying formats. I've seen some suggestions for transpose but can't seem to apply them to my use, hoping for help. So it looks fine in Google Sheets itself, and functions perfectly when copied to other Google apps. In this comprehensive guide, we will explore the CONCAT formula in Google Sheets. Formula Breakdown: =CONCATENATE: The function Google Sheets will use to combine the two given text cells. 0. The result is shown in the picture below. Open a sheet with data, and click right beside the cell where you want to insert a merged column. The CONCATENATE Function. ADD; 3 of 18. So, in our case, the CONCATENATE Google Sheets formula will look like this: =CONCATENATE(A2," ",B2) Google Sheets CONCATENATE multiple cells (more than 2) Let’s move further and concatenate a few more cells into. This could be a string, or an array of strings in a range. How to Concatenate Text and Formula in Google Sheets. ; Combine necessary cells of data together Concatenate records from the selected range into one cell. 2. Keep line breaks with concatenate? 1. Using the JOIN Function to Concatenate with Separators. Returns the text with the non-printable ASCII characters removed. In those instances where a row in table “A” has no matching row in table “B”, you get an empty cell. The character double quotation mark is associated with the number 34. Using the Ampersand Symbol to Concatenate with Separator in Google Sheets. CONCATENATE Text and Number or Numeric. valor2 - O valor a ser anexado a valor1. The difference between the INDIRECT function and a typical direct function is that a typical function directly references a cell (or range of cells). CHIFFRE. If you want to add a space between the text, just put the “ ”. This can go to an infinite number of rows. 1. 您有在公司或學校使用 Google 文件等 Google 產品嗎？. Problem with concatenate function in google sheets. “ SELECT * ” returns all the columns of the sheet. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the commas. ]) Here's a breakdown of the arguments: delimiter: This is the character or string that will be used to separate the values in the final text string. The Ampersand (&): The Basic Way to Concatenate Number and String in Google Sheets. Você está usando os produtos do Google, como o Documentos Google, no trabalho ou na escola? Consulte dicas, tutoriais e modelos úteis. You can use the following basic formula to concatenate a range of cells with a comma in Google Sheets: =TEXTJOIN(", ", TRUE, A2:C2) This particular example concatenates each cell in the range A2:C2 with a comma in between the characters of each cell. 2. The format you just created can now be used on other. Alternative: The TEXTJOIN Function. For example, we have in the screenshot below, a list of first and last names. Follow the steps below to join text items using a space as the delimiter. Mix data of multiple columns into one column. We can type the following formula into cell B2 to wrap the string in cell A2 in double quotes: =CONCATENATE("""", A2, """") We can then drag and fill this formula down to each. This time, you need to place a reference to a cell with the existing record first, then append the new text: =A2&", US". We can add text both after and before the given formula. CONCATENATE ("Chào_mừng"; " "; "đến_với"; " "; "Trang_tính!") CONCATENATE (A1;A2;A3) CONCATENATE (A2:B7) CONCATENATE (chuỗi1; [chuỗi2;. The CONCAT function is a built-in function in Google Sheets that allows you to combine data from multiple cells into one cell. Add-ons for Google Sheets Add-ons for Google Docs. How can I get a QUERY to CONCATENATE text after it combines data? 1. Here's a list of all the functions available in each category. Usage: CONCAT formula in Google Sheets. ”). Search. Excel concatenate multi-line text with next column multi-line. g. Google Sheets function list; 2 of 43. 00 "lbs" (note the quotes around lbs) Click Apply. Learn how to join things together in Google Sheets by using the CONCATENATE, CONCAT and AMPERSAND functions. Is there a method to insert line breaks and have 'blank. SORT sorts a vertical array, 1 = single column and FALSE reverses the order. Now, we should start off our function with the equals sign ‘=’ and enter the name of the function we will use (remember that you cannot use wildcard characters with all Google Sheets functions). Here is the second flexible array formula to join columns in Google Sheets. Se estiver vazio, o texto será simplesmente concatenado. One way to use TEXTJOIN is when you have columns containing. ]) – Các chuỗi bổ sung cần kết hợp liên tiếp. ]) Chris Daniel Last updated September 29, 2022 If you’ve just switched over to Google Sheets from Excel, you’re likely already familiar with the CONCAT function. For example, = {SUM (A1:A10), SUM (B1:B10)} will produce two values. I want to know how I can take a string literal such as. Create a new column on the side of the LONG column and apply the formula: =CONCATENATE (D1, ", ",E1 ) We are using CONCATENATE because we will want to separate the values with a comma - making it three strings to put together. CONCATENATE: Appends strings to one another. Clear searchIn this article, you will learn various ways to concatenate text strings, numbers and dates in Excel using the CONCATENATE function and "&" operator. 將多個字串連接在一起。. Create a simple formula to concatenate text. We can make use of the TEXT function to combine a 12 hr or 24 hr start time with a 12 hr or 24 hr end time in Google Sheets. column B: ba, bb, bc. Concatenate column with identical characters between rows. Google Sheets. 2. Combining multiple cell values on Google Sheets. We can also use array formulas to combine columns and concatenate the text contained in them. dynamicRange. Type the address of the other column that you want to combine with, such as B1:B. Open a sheet with data, and click right beside the cell where you want to insert a merged column. Formula Breakdown: =CONCATENATE: The function Google Sheets will use to combine the two given text cells. You only need to change the range in the above formula on your Sheets. When you truly want to attach double quotes to a text and have Google Sheets perceive it as text alone, this presents a major challenge. CHIFFRE. 1. It creates IS THIS NO TRICK from the input in A1. For example, if I had the following data. The INDIRECT function in Google Sheets takes in the cell address in the form of text and returns a cell reference. Right click the cell and tap Insert Column from the drop down menu. You can use the CONCATENATE and IF functions combined. This help content & information General Help Center experience. . This is a true array, insofar as Array. 1. To start, enter some text into cell A1 and the formula in cell B1, to reverse the order of the text: Reverse Text In Google Sheets. Step 2. You may also use the CONCAT. Google Sheets - Query - Concatenate text to returned data. In this formula, the ranges A2:A and B2:B specify the columns for first and last names. ARABE (ARABIC) Fonction ASC. ”. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel. We do this by using a “+” as the boolean operator OR inside the second argument of the FILTER function, as follows: =FILTER (A1:B10, (A1:A10="alpha")+ (A1:A10="gamma")) Now, anytime either condition of the filter is satisfied, in other words an “alpha. There is concatenate which takes multiple parameters, so only one call is needed: =concatenate(sum(1+1), "|", multiply(1,5)) Even shorter is the binary concatenation operator & , which is what I would use:Google Sheets - Query - Concatenate text to returned data. 1. Here we are going to use Query aggregation of text strings. Suppose we have the following dataset in Google Sheets: We can use the following formula to query for. 0. concat (range2); } And then simply:Most of the existing answers use text string manipulation and split() which is undesirable because they may cause side effects such as converting the text string 1 2 3 to the date 2 January 2003. Step 2. You can also use the arrow buttons on your keyboard to navigate to the desired location. . The INDIRECT function in Google Sheets is used to convert text strings into valid cell or range references. The strings can be text, numbers, or single-cell references. Without delimiters =concatenate(data_range) With delimiters. 70+ professional tools for. In Google Sheets, multiple ways exist to concatenate two more strings in a single cell. For example, the INDIRECT function will convert the text string “A1” into the cell reference A1. For example, let's say you have a dataset with two columns: "First Name" and "Last Name. Does anyone know how to do this, or what an alternative method might be? ¿Usas productos de Google, como Documentos de Google, en el trabajo o en clase? Prueba estos eficientes consejos, tutoriales y plantillas. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the commas. =CONCATENATE ("Pay an amount of ",A1) The result of this formula would be like “Pay an amount of 2500. The CONCATENATE function is designed to join strings together. 3 Ways to Concatenate with Separator in Google Sheets. This could be a string or an array of strings in a range. log of concatenatedDateStartTime looks like:This help content & information General Help Center experience. See Also. Let’s see how it’s done. Take Note! Summary. Join a list of values into a single cell. Google Sheets Text Join to consolidate rows into columns. The challenge is that I need to concatenate all the occurrences into one single cell. 06') and then use it in a formula to reference information for my second sheet?Related: Merge Duplicate Rows in Google Sheets and Concatenate Values. In the search and replace dialog use these valuesFormula: You can use this Query to concatenate a large number of columns in Google Docs Sheets. 1. Repeated Images with REPT Formula in Google. This function is particularly useful when you want to create a concatenated text string from a range of cells or an array of values. 1. 1. You will get the result, Mr. Google Sheets - Query - Concatenate text to returned data. Using Built-In Functions to Concatenate Strings in Google Sheets. I have 4 columns in my Google sheet that I use 'concatenate' with (so the contents of each row from the 4 columns shows in a single cell at the end of that row). 0. Click on any cell to make it the active cell. Learn how to join text (combine text) in Google Sheets. Select the range of cells that you want to concatenate with line break, and then click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot: 2. We can type the following formula into cell C2 to combine the date in cell A2 with the time in cell B2: =CONCATENATE (TEXT (A2,"m/d/yyyy")," ",TEXT (B2,"h:mm AM/PM")) We can then click and drag this formula down to each remaining cell in column C: Column C now displays the date and time. 55”. Then you can use this column in the query. Locate and click on Extensions > Add-ons > Get Add-ons. If you have ever tried to combine columns in Google Sheets only to find that the data has been merged together in one column instead of two, you are not alone. Using the JOIN Function to Concatenate with Separators. I have one date field that has varying formats. The CONCAT function takes the text values in the two cells and turns them into a single string. You will see the output as shown above. Use the below formula in cell B2. The Ampersand (&): The Basic Way to Concatenate Number and String in Google Sheets. ArrayFormula. It works in the opposite way to the ADDRESS. These values are merged in the same order as they appear in the statement. The CONCATENATE function takes 1 or more arguments and only works on strings. I have 4 columns in my Google sheet that I use 'concatenate' with (so the contents of each row from the 4 columns shows in a single cell at the end of that row). Time Formatting and Combining Using TEXT and Ampersand:I am trying to concatenate some block of text with prefix and suffix text. I can produce a similar result using REDUCE (I can't believe I'm writing a reducer in a Google Sheet): =REDUCE("", B2:E2, LAMBDA(accumulator, current_value, CONCATENATE(accumulator, current_value, char(10)))) However the lambda function doesn't seem to provide the index of the current iteration, so I'm not sure how to grab the. Let us now go ahead and look at a few examples that we tried on Google Sheets application. I'm inserting line breaks using CHAR(10). ¿Usas productos de Google, como Documentos de Google, en el trabajo o en clase? Prueba estos eficientes consejos, tutoriales y plantillas. 00") produces 012. To use the CONCAT function, you will need to open up your Google Sheet and select the cell where you want. text2,. Aggregate Strings in Google Sheets with Query – What it Means? Let me clarify my concept first with an example. Next, type the equal sign = to begin the function, followed by ARRAYFORMULA and an opening bracket (. So, all you need to do to make the formula work with the other cells is drag it downward. TEXT (number, format) number - The number, date, or time to format. I would like to preserve the formatting of the font color if that's possible, as it will save me a lot of work when. 撰寫訊息中. arrayN) Parameters. It requires some helper columns, but here's a solution without VBA. CONCAT; 4 of 18. Q&A for work. In this tutorial,. As an alternative to CONCATENATE you can use the ampersand operator. TEXTJOIN in Google Sheets (Easy Guide) - Open Google Sheets. The tutorial shows how to use the TEXTJOIN function to merge text in Excel with practical examples. I already have a working formula which returns the required block of text. Note that you may need to replace spaces with "&" if your text has. Take the following steps to combine columns horizontally in Google Sheets using the concatenate function. 1. Open a new Google Sheets spreadsheet on your computer. If I manually add a newline using the shortcut cmd + opt + enter then the expected output as shown:. Create a CSV file. This will open a new window. Tap the cells in the order you want to combine the cells. Note: In Google Sheets, CHAR(10) is used to represent a line break. The CONCATENATE function syntax:The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide delimiter or IgnoreEmpty arguments. To use the JOIN function: Select the cell where you want to merge the data. text2,. TimeStamp First Name Last Name Business Name Formula 01/10/21 John Doe Test Company ==. The second argument is B2 that consists of the last name. Press Alt + Enter or Ctrl + Enter. Or, drag and select a range. Note: This feature is available on Windows or Mac if you have. Combining multiple cell values on Google Sheets. Ajuda do Editores de Documentos Google. 0. SPLIT creates and array in a row ("virtual" if the formula is used as shown) of the words in A1 separated by blanks. At the current time, in Google Sheets you cannot bold partial components of any formula. 06') and then use it in a formula to reference information for my second sheet?Related: Merge Duplicate Rows in Google Sheets and Concatenate Values. You should see a small box on the bottom right-hand corner of. Search. The JOIN function simplifies combining a list of values in separate cells into a single cell separated by an appropriate delimiter. With slight adjustments, all the ways I mentioned are capable of merging columns in Google Sheets. Its syntax is =CONCAT(cell1,cell2) Where cell1 and cell2 contain the data. Google Sheets - Query - Concatenate text to returned data. The first argument you put is cell A2, where the first name is stored. If you want to combine these two columns in one go, you can simply use the array formula with the. How to Concatenate a Select with the query()? 2. Until recently, there were two prevalent methods to merge cell contents in Excel: the concatenation operator and CONCATENATE function. 7 Suitable Methods to Concatenate Text and Formula in Google Sheets 1. Even though you might encounter some challenges when combining cells that feature text and numbers in Google Sheets, we have found a pretty. Regardless of whether the values are text or numeric. Uses diesen in CONCATENATE, Google Sheets hires me combine date and time in the desired format with other characters or text: =CONCATENATE(TEXT(B2,"D MMM YYYY"),", ",TEXT(C2,"HH:MM:SS")) Combine columns in Google Sheets. The user can specify the choice of a delimiter. 1. Google Sheets function list; 2 of 18. Community. To start, enter some text into cell A1 and the formula in cell B1, to reverse the order of the text: Reverse Text In Google Sheets. So in cell B1 I want to do: =CONCATENATE("\"",A1,"\"") OOCalc does not like this, or without the escaping backslash. TEXT (number, format) number - The number, date, or time to format. Add a comment. The JOIN function simplifies combining a list of values in separate cells into a single cell separated by an appropriate delimiter. When using them, don't forget to add quotation marks around all. The most typical use case is joining the first names and last. Like in Microsoft Excel, you can use the CONCATENATE function to combine text in one cell with text/number/date in another cell in Google Docs Spreadsheets. We can easily use separators such as space in the. Open Google Sheets to the spreadsheet with your data. In the dialog boks, enter 0. Array formula for cell C1 (must be saved using CTRL + SHIFT + ENTER ):First, we will use the CONCAT function. Open a new Google Sheets spreadsheet on your computer. Você está usando os produtos do Google, como o Documentos Google, no trabalho ou na escola? Consulte dicas, tutoriais e modelos úteis. Across all the examples, we’ll notice that the CONCAT formula appends the second value after the first. JOIN: Verknüpft die Elemente eines oder mehrerer eindimensionaler Arrays und verwendet dabei ein angegebenes Trennzeichen. Please note that it’s not possible to use Concatenate or any other text join functions in Query. text1 - Any text item. , A7,B7). ASC function; 4 of 43. Saiba como trabalhar nos arquivos do Office sem instalar o Office, criar planos de projetos dinâmicos e agendas de equipe, organizar automaticamente sua Caixa de entrada e muito mais. Select the cell where you want to put the combined data. The primary purpose of the TEXT function is to showcase data in a more readable, user-friendly format without altering the original data. To use CONCAT, open your Google Sheets spreadsheet and click an empty cell. Suppose we have the following data in Google Sheets: We can use the following formula to concatenate cells in column A and B only if the value in column B is equal to “Good”: =CONCATENATE (IF (B2="Good", A2:B2, "")) The following screenshot shows how to use this formula in practice: If the cell in column B is equal to “Good” then the. TEXTJOIN is useful when consolidating data from multiple cells into one. 1. Now, refer to the first cell of the second column. For example, say you have a list of first names and last names, and you want to combine them to get the full name in one cell. ExampleBut you can concatenate a number without losing its format by using the TEXT function in Google Sheets. 2 Concatenating. 4. I used the CONCATENATE function, but it will only take a full cell, not a first letter. CONCATENATE text strings and numbers in Google Sheets; Google Sheets: join strings with separators; Concatenate with line break in Google Pages; Combine date and time in Google Sheets; Combine columns in Google. Connect and share knowledge within a single location that is structured and easy to search. Click on the first extension, as seen in the. 1. To insert the same to all other cells, ArrayFormula will also help: =ARRAYFORMULA (A2:A7&", US") Tip. It can speed up production, save time, and ultimately benefit the person greatly. However, one way in which you can make it look like that is by first in the hyperlink like below: =HYPERLINK ("& CHAR (10) & "Link to Google") This will make the entire content of the cell a hyperlink. To use Google Sheets Concat, first select the cells that you would like to combine into a single text string. 1. The following VBA code bolds cells that contain a specific phrase. Apply multiple font colors to the text in a single Google Sheets cell (4 answers) How to convert VBA script to Google Apps Script automatically? (3 answers) Closed 2 years ago. Issue with Google Sheets: I have the CONCATENATE function combining text from several cells into 1 "copypastable" block. For example, I want to convert August 10, 2023 to a number. Type in the initial part of the formula, which is =CONCATENATE (. The second worksheet builds a payroll report, and directly references columns in the first sheet like so: ='Sales Report - WB 10. function, which returns an address in text format. column A: aa, ab, ac; and. Issue with Google Sheets: I have the CONCATENATE function combining text from several cells into 1 "copypastable" block. This help content & information General Help Center experience. CONCATENATE (文字列1, [文字列2,. & tells Google Sheets to combine the two text functions. To combine columns horizontally in Google Sheets, follow these steps: Type =ARRAYFORMULA ( to begin your formula for combining columns. Syntax. It should look something like this: =CONCATENATE (B2,“, ”,A2) How to use the CONCATENATE function in Google Sheets - CONCATENATE cells with comma. Search. If you want to combine text from two or more cells into a different cell (concatenate the values), you can do this with a simple formula. The CONCATENATE function is a Google spreadsheet function that combines two or more text strings into a single string. The formula is: It gives the answer 10 in the following example because that’s the value in cell A1:var mergedArray = array1. The following examples show how to use each method in practice with the following list of strings in Google Sheets: Example 1: Wrap Quotes Around Quotes. Another way for Google Sheets to convert date to number is by using the DATEVALUE function: =DATEVALUE (date_string) where date_string represents any date in the known for spreadsheets format. Here is an overview of what we will archive:If I wrote a formula in a cell: =CONCATENATE("'",TO_TEXT(B2),"'") I will get the 'B2_STRING' Example: The B2 has a name, so I end up with 'Name' But now I need this concatenation inside a VLookup . Cell 2: This will be the second cell reference, the text will be joined to the end of Cell1 text. Type a formula to merge the first cells in each column (most likely, these will be cells from row 2 unless your dataset doesn’t have a header row). Let's suppose I had the following text contained in Cell A2 of a spreadsheet: Case Bakers' Flats 12" White Flour Tortillas 10/12ct and needed to put the following formula into B2:. The Best Way to Combine Strings – TEXTJOIN You need to follow below steps: First, enter the TEXTJOIN function in a cell. Here I am selecting Textjoin, as it can omit blank cells. Merged text from partsReturns a new text value consisting of each part merged together in the order specified. Google Sheets - How to concatenate cells that meet a condition. Type =CONCAT (CellA,CellB) , but replace CellA and CellB with your specific cell references. Bold Text in the Output of Concatenate Formula. This help content & information General Help Center experience. Enter the CONCATENATE function, including the cells you want to combine. Google Sheets QUERY built-in function automatically escape some characters by internally adding before single quotes but it's doesn't work when the cell value. It specifies which columns you want to return and in which order. Example Sheets. Google Sheets Tutorial En Español - CONCATENAR, JOIN, TEXTJOIN, CONCAT, & - Unir Cadenas de textoEn este tutorial aprenderas a unir textos o cadenas de texto. Learn how to join things together in Google Sheets by using the CONCATENATE, CONCAT and AMPERSAND functions. For our example , the formula will be =C6&”. In this tutorial we'll cover how to use Google Sheets Join function, TEXTJOIN function, & operator and. If you attempt to concatenate (join) a date with text, the date will be displayed as a number: =CONCATENATE(B3,C3) This is because Excel stores dates as serial numbers; when you join a date with text, the cell. Select the cell you want to combine first. Add both prefix and suffix text into multiple cells: To add both prefix and suffix texts into the cells, please apply this formula: =arrayformula ("Extend-" & A2:A6 & "-Addin") into a blank cell, and then press Enter key, and the cells have been added prefix and suffix texts into the cell values, see screenshot: Note: In the above formula. Alternatively, you can use the ampersand. The name of the function we will use in this guide is SUMIF. The CONCAT Function. Click on the cell you wish to add your combined cells to. Trying to apply CONCATENATE via ARRAYFORMULA on Google Sheet. Applying Ampersand Operator. CONCATENATE: Appends strings to one another. Ask Question Asked 22 days ago. Press = (the equal sign). The CONCAT function then takes these two values and puts them together. In Google Sheets, CONCATENATE is a powerful function that allows users to combine text strings, values, or cell references into a single string. Google Sheets: Concat text to username in each cell but sometimes there's more than 1 username per cell. ”) Since CONCATENATE simply merges all the text in order, we need to explicitly add spaces between the values to get the result we need. 1 of 18. 0. However, you use the TEXT function to specify that the date should remain formatted as a date: = CONCATENATE (A2, TEXT (B2, "M/DD/YYYY")) This particular formula will concatenate the strings in cells A2 and B2 and keep the date. If the string is one that Google Sheets recognises as a date type, it treats it as a number value for those functions requiring that. However, you use the TEXT function to specify that the date should remain formatted as a date: =CONCATENATE(A2, TEXT(B2, "M/DD/YYYY")) This particular formula will concatenate the strings in cells A2 and B2 and keep the date in cell B2. Click on the cell you wish to add your combined cells to. Get insights together with secure sharing in real-time and from any device. Aggregate Strings in Google Sheets with Query – What it Means? Let me clarify my concept first with an example.