OmniDocHow to merge multiple rows of content into one row in Excel and retain all the content
When the same person or the same type of records are scattered in multiple rows, how to use a formula to merge these contents into one row and ensure that each value is retained in the result. The article gives the steps of the TEXTJOIN formula, the alternative writing method of the old version, and the verification method after the merger.
Key points of this article
- Merge multiple rows into one row by grouping column. First use a formula to generate the merged text, then paste it as a numerical value, and finally delete the duplicate rows without losing the original data.
- Excel 2019 and above versions can use the TEXTJOIN function. Older versions need to enter formulas in arrays or use connectors instead.
- Back up the original table before merging, and confirm that the delimiter does not conflict with the content itself.
- Dates and numbers will become text after being combined, and the format needs to be converted in the formula first.
- After merging, compare the number of lines and the number of delimiters to confirm that each piece of original content appears in the result.
In many tables, the same person, the same order or the same project occupies several rows. For example, a person has three contact information, and a customer has multiple messages. When you need to export it into a format with one person per line, directly copying and pasting will overwrite the content, and manual splicing is easy to miss. Merging multiple rows of excel content into one row is a common need for organizing this type of table.
This article is written for people who need to merge multiple rows of data into one row without losing any content. After reading, you can use a formula to put the same group of content together, and then organize the results into clean rows and records. The method is based on the general functionality of Excel and does not rely on specific plug-ins.
How to merge multiple lines of content into one line without losing data? One sentence conclusion
Use the TEXTJOIN formula to join the contents of each row according to the grouping column, then paste the result as a numerical value, and finally delete the duplicate grouping rows. This way the original content of each row will remain in the merged cell.
| your situation | Recommended practices | reason |
|---|---|---|
| Excel 2019 or 365, multiple rows for the same person | TEXTJOIN plus IF condition | The formula takes the value directly by name, no need to sort first |
| Old version of Excel, not many rows | Connectors & frame-by-frame splicing | Does not rely on new functions, the formula is easy to understand |
| Keep the date or number after merging | First use TEXT to convert to text and then splice it | Prevent the date from turning into a string of numbers |
| The amount of data is huge and needs to be updated repeatedly. | Power Query merge by group | After the original data is updated, it can be refreshed with one click |
| The data is a table in PDF | Export to Excel first and then process | The table structure needs to be converted into cells first |
Before you start: Confirm these points first
Making a few judgments before taking action can avoid rework after the merger.
- Group basis should be able to distinguish each person. If two people have the same name, the formula will combine their contents together. It is recommended to select fields that can distinguish records, such as job number and order number, as grouping columns. When using only names, check whether there are duplicate names first.
- Confirm Excel version. TEXTJOIN is only available in Excel 2019 and above and 365. When the version is uncertain, you can try entering a formula in a blank cell. If #NAME? is displayed, it means it is not supported.
- Check whether the delimiter will conflict with the content. If the content itself contains commas or semicolons, it will be difficult to distinguish each item after merging. It is recommended to choose a symbol that does not appear in the original text, such as "|".
- Back up the original table first. The merge process will change the data and retain an original worksheet, which can be directly compared when errors occur.
- Confirm whether the data is a pure table. If the source file is a PDF, the table may only be arranged visually, first make sure it can be exported as an editable table.
Steps to merge multiple rows into one in Excel
The following example takes column A as the name, column B as the content, and data from row 2 to row 20.
- Make a copy of the worksheet as a backup and work on the copy.
- Sort by name column so that rows of the same person are adjacent for easy checking later.
- Enter the formula in cell C2: =TEXTJOIN(",",TRUE,IF(A$2:A$20=A2,B$2:B$20,"")). The meaning of the formula is to connect all the contents of column B where column A is equal to A2 with commas.
- Just press Enter in Excel 365; in the old version, you need to press Ctrl+Shift+Enter to confirm and let the formula run as an array.
- Fill in the formula of C2 down to the last row, and each row of the same person will display the complete merged result.
- Select column C, copy it and use the "Value" in "Paste Special" to overwrite the original position and remove the formula dependency.
- Select columns A and C and use Remove Duplicates to judge only by the name column, keeping the first row in each group.
- After confirming that the results are correct, delete the unnecessary auxiliary columns and change the title of column C to "Merge content".
If the version is older and only a few lines need to be merged, you can also use connectors: =B2&", "&B3&", "&B4" to manually connect each line together. The logic is intuitive and suitable for small-scale data.
Check each item after completion
Do not deliver directly after merging. Please check it according to the form below first.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Number of groups | The number of unique names calculated by deleting duplicates before merging, compared with the number of rows after merging | If inconsistent, check whether there are blank names or duplicate names. |
| Complete content | Count the number of delimiters in the merged cells. It should equal the number of original rows in the group minus 1. | If the quantity is less, it means that the blank content is ignored, or the grouping column is filled in incorrectly. |
| Date and number | See if there are five-digit strings in the merge result, such as 45000 | In the formula, TEXT (B $2:B$ 20, "yyyy-mm-dd") is used to convert the format before splicing. |
| Special symbol | Search for delimiters to make sure there is no same symbol in the content | Change to an unusual delimiter and remerge |
| Cell length | Check to see if any extra-long text has been truncated | If the content is too long, consider changing it to multiple columns or splitting it to another table |
The merged content is text and cannot be directly involved in summation. If subsequent calculations are required, they should be calculated in the original data, and the merge table is used for presentation and export only.
# which method is suitable for you?
- only organize the form once in a while : you can use TEXTJOIN or connectors, and paste it as a numeric value after completion, so that there will be no errors due to the formula when sharing files. - the form is updated weekly : it is recommended to make the data into a standard table and use formulas or query tools to generate merge results on a regular basis to avoid manual processing each time. - the data comes from PDF**:. First, organize the tables in PDF into Excel format, and then merge them. You can use the PDF editor of the OmniDoc desktop client to export PDF to Excel, and then follow the steps above. Download address is download.omnidoc.top.
# frequently asked questions (FAQ)
1. Will the original multiple lines be deleted after the merger?
No, the formula just puts the content into a cell. Duplicate lines are deleted only when you perform Delete duplicates, so make a backup copy before deleting.
2. Excel merges multiple lines into one line, is there any way to keep line breaks?
You can replace the dot sign with CHAR (10) in the formula and turn on the automatic line wrapping of the cell. When displayed in this way, each item will be divided into lines, but it is important to note that the line height should be adjusted manually, and make sure that the newline character is retained when exporting.
3. Can the merged content be split back into multiple lines?
You can use the "separate" function to select the same separator as at the time of the merge, and you can split it into multiple columns. If you want to detach multiple rows, you need to use transpose or query tools, the operation will be more complex, so it is recommended to keep the original table.
4. Can the numbers still be calculated after merging?
Cannot be calculated directly because the result of TEXTJOIN is text. The calculation should be done on the original data, and the merged results are only used for presentation or export.
5. Do the results of the merged formula need to be checked manually?
Need. The formula does not identify whether the content is incorrect, especially delimiter conflicts and duplicate grouping. Use the checklist above to read it item by item, and it is recommended that several groups of important data be checked and compared with the original table.
# Summary
Excel multi-line content is merged into one line, the core is to first use TEXTJOIN or connectors to generate merged text, and then paste as numeric values and delete duplicate lines. Backing up before the operation and checking the number of lines and delimiters after the merge ensures that every piece of content is preserved.
