You can combine more data from different into a single cell. There are many ways we can combine data into one cell, for example, "The ampersand symbol" the CONCAT function.
The steps to combine multiple data from different cells into a single cell
1. Open up your workbook.
2. Select the cell you want to put all your data.
3. Type = and select the first cell you wish to combine.
4. Type & and use quotation marks with space enclosed.
5. Select the other cell you want to combine and hit enter. For example =A3&" "&B3.
This works only when you want to combine two cells into one cell.
Steps using the CONCAT function.
1. Open your worksheet.
2. Choose the cell you want to combine the data with.
3. Write the formula =CONCAT(
4. Select the cell you want to combine first.
You use commas to separate the cells you are combining and use quotation marks to add spaces, commas, or other text.
5. Close the formula with a parenthesis and hit enter. An e.g. might be =concat(A2, "doctors").
If you have more texts and you want them to fit into one cell, it's easy. If you put the whole line into one cell, it will keep going, so what you need is a way to organize your work and fit the actual data into one cell. Mostly is done by wrapping text like a paragraph or inserting a line break within the cell.
Steps on how to wrap the text to fit into a cell.
The only thing you have to do is format the text so that the text will wrap automatically.
1. Right, click within the cell.
2. A menu will pop up.
3. Format cells, a dialog box open, move to the alignment tab, and check the box next to the wrap text.
4. The text within the cell will wrap automatically.
Inserting the line break within a cell
The line break will enable you to break every sentence when you want it and how you want them to appear.
1. Type every sentence within the cell.
2. To insert a hard return, press ALT-ENTER. (On a Mac, navigate like CTR-OPTION-ENTER. Or just hit command and enter.
To avoid readability problems, the data in a cell will be aligned at the bottom of the cell Control vertical lines.
3. Select all the cells to align.
4. Right-click and the menu pops up. As it was in the first procedure.
5. Select format cells and then go back to the alignment tab.
6. See the drop-down menu and align all the content within the cell vertically.
Combining CONCATENATE and TRANSPOSE Functions
The CONCATENATE function is a vital tool that allows you to perform various Excel operations, including combining data from different cells into one cell. Therefore, when putting multiple data into one cell, you may also want to transform the layout to fit the cell. This is where the TRANSPOSE function comes in. In this case, the TRANSPOSE function will change the data layout while the CONCATENATE function will combine the data. The syntax for both functions are:
TRANSPOSE(array) and
CONCATENATE(text1, [text2], …)
Steps:
1. Select the cell where you want to store the combined data from multiple cell rows.
2. Write in the following TRANSPOSE formula first:
=TRANSPOSE(C4:C7)
The formula indicates that you want to combine data from cells C4 to C7. That means it can change based on the range of cell rows you want to combine into one cell.
3. Press the F9 button and you will see the data in cell D4 within curly braces.
4. You can now remove the curly braces and use the CONCATENATE formula to combine all the selected rows without spaces. The CONCATENATE formula to use is:
=CONCATENATE(“text1”,”text2”,”…”),
where
Text1, text2, …represent the data from all cells within the column. This is the dataset you want to combine.
5. Next, you can now make the data from multiple rows clear by inserting a comma (,) and a character (and) between the last two data placed within double quotes (“ ”). The final formula will change to something like this:
=CONCATENATE("text1"," ","text2"," ","text3"," and","text4").
Using the TEXTJOIN Function
The TEXTJOIN function allows you to put up to 252 strings of data in one cell. Its syntax is:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …), where;
A delimiter is the text separator such as comma, space, and character
ignore-empty uses the TRUE or FALSE result. If the result is TRUE, it will ignore the empty value, while if the result is FALSE, it will include empty values.
Steps:
1. Select the cell where you want to store the combined data. For example, if you want to combine a cell range like B4:B7, you can select cell C4.
2. Write the TEXTJOIN formula in the selected cell. You can use the following formula:
=TEXTJOIN(",",TRUE,B4:B7)
3. Alternatively, you can type in the cells one by one, but remember to use a separator (,). In this case, the formula will change to:
=TEXTJOIN(",",TRUE,B4,B5,B6,B7)
4. Press the Enter button and you will see combined data in the cell.
Using The Formula Bar
When using the Formula Bar to put multiple data in one Excel cell, you will need to copy all the data from the rows and paste them into a notepad. From here, you can now copy the rows again and paste them into your Excel formula bar. That is because the Excel sheet only copies cell by cell.
Steps:
1. Copy all the cell values or data you want to combine and paste them into the Notepad.
2. Copy the values again from the Notepad.
3. Go back to your Excel worksheet and place the cursor in the Formula Bar.
4. Right-click on your mouse to paste the copied rows in the bar and click the Enter button.
5. The step will put all the rows in one cell.
That is all, and your data will be clean and readable. To fit all the data into one cell is annoying when you don't know how to wrap text or insert a line breaker. Sometimes it is challenging to copy and paste. That will be the next tutorial on copying and pasting the data within a cell that is embedded with hard returns.