How To Add 3 Text Cells Together In Excel
Bottom Line: Learn how to apply formulas, functions, and Power Query to combine multiple cells or columns into a single column and include separator characters.
Skill Level: Intermediate
Sentinel the Tutorial
Download the Excel Files
I've included both the Before and After files for you to download and exercise on, if you like.
Combine Text – Begin.xlsx
Combine Text – FINAL.xlsx
Combining Text from Multiple Columns
Recently, we've been looking at unlike ways to take information from one cavalcade and split up it out into two or more than columns. We used the example of a full proper noun cavalcade that we wanted to split up into showtime and concluding names. We explored how to split columns using, Power Query, Text to Columns, Formulas, and Flash Fill.
Today's tutorial is inspired by comments from Prof YC and Mohamad on our YouTube channel asking if we can do the opposite.
These are great questions and I'grand excited to show you three different ways to go near doing this.
1. Formula Using Ampersand (&)
Compatibility: All versions of Excel on all operating systems.
The beginning way to become about combining text is by using a simple formula. To bring together cells together we use the ampersand symbol (&). Joining the contents of cells A2 and B2 would look similar this: =A2&B2.
Merely to split up the offset name from the concluding name in the output, we utilise the space graphic symbol wrapped in quotation marks and add together another ampersand. The formula would read: =A2&" "&B2
Perhaps, you'd rather the final output has the last proper noun first, then a comma, and then the starting time name. If so, y'all tin alter your formula to switch the cell order and add a comma before your infinite: =B2&", "&A2
2. Formula using the TEXTJOIN Office
Compatibility: Excel 2019 or afterwards including Microsoft 365 on all operating systems
What if you have 3 columns and not all of the cells have data in them? Certainly, you could add another cell into your formula with some other ampersand, merely anytime you had a blank cell, you would also take an additional space character in your output. To avoid this, you can apply a formula with the TEXTJOIN role (bachelor Excel versions 2019 and subsequently).
The TEXTJOIN function has three arguments.
- The starting time statement is delimiter. This is the grapheme (or string of characters) that you want to appear betwixt the text in your cells. In our instance of names, we want them separated by a space, and then nosotros type " ".
- The second statement is for ignoring empty cells. You choose either true or faux, depending on if you lot want Excel to disregard cells that are blank. Nosotros do, then we will blazon TRUE.
- The third argument is text. These are the cells you desire to combine. Yous tin can select them individually, or select an unabridged range.
All together, our formula is written: =TEXTJOIN(" ",TRUE,A2:C2)
iii. Power Query
Compatibility: Excel 2010 or afterwards for Windows
The Merge Columns feature of Power Query is another great way to chop-chop combine multiple columns and add a separator character.
- To combine the contents of cells using Ability Query, start by going to the Information tab (Ability Query tab for older versions of Excel).
- Cull the option that says From Canvas or From Table or Range (depending on your version). That will open up up a preview of your data in the Power Query Editor.
- Select the columns that you want to combine.
- Then select Merge Columns on the Add together Column tab. That will bring upward the Merge Columns Window.
- Select your choice for how you want the text from each column to be separated. In our case, nosotros want a space between the names.
- Yous can besides proper name the column from this window.
- Hit OK.
A new column with the merged text volition be added to the preview.
Power Query automatically detects and skips over any blank (naught) cells when information technology combines the columns.
If you instead wanted the format to exist "Last, Outset" in your merged cavalcade, the procedure is similar. The club in which you select your columns earlier selecting Merge Columns is the aforementioned society that they will appear in the output. Instead of selecting Infinite for your Separator, choose Custom, and then type a comma and a space.
When you lot hit OK, the new merged column will appear with the desired format.
If your preview looks the way you desire it to, you lot can click on the height half of the Close & Load push to export it into your worksheet.
Note: This process I've described adds your new column to the existing columns on the output sheet. If you want to replace them instead, you simply choose the Merge Columns option on the Transform tab instead of the ane on the Add together Column tab.
If you're new to Power Query, here is a guide to installing Power Query if you are on an older version of Excel. And bank check out my free webinar below to learn how to get started with Power Query and the other modernistic Excel tools/features.
Free Webinar on Power Query and Modern Excel
If you are relatively new to Power Query, I take a free webinar going on that we'd dearest to accept you to join. Information technology covers all the power tools, including Power Query, Power Pivot, Ability BI, every bit well as macros, VBA, and pivot tables. You tin can admission it hither: The Modern Excel Pattern
If you are interested in seeing another awarding of the TEXTJOIN function, check out this tutorial: 3 Ways to Brandish (Multiple Items) Filter Criteria in a Pivot Table
I hope you've learned a bunch and can put the noesis to apply! I desire to help you exist an Excel Hero in your workplace. If you have comments or questions, please go out them below.
How To Add 3 Text Cells Together In Excel,
Source: https://www.excelcampus.com/powerquery/combine-text-formulas-power-query/
Posted by: smithtrate1937.blogspot.com
0 Response to "How To Add 3 Text Cells Together In Excel"
Post a Comment