Combine rows of text in Excel into a single row without losing the data - Excel functions do not work

Excel 365 Pro Plus with Power Pivot and Power Query.

Concatenate blocks of 3 to 5 rows of data into single rows.

No formulas, no VBA macro.

Database is 400 rows of painfully typed in unique names and addresses.

https://www.mediafire.com/file/5f0wpp1t1kbesks/08_29_22.xlsx/file

https://www.mediafire.com/file/uun374kxd5ppb7l/08_29_22.pdf/file

The reason that only the formula is displayed is probably because the cell where the formula resides is in "Text" format.

Before entering the formulas, select the column where you require the formulas and use the "Number format" to format as "General"

If you already have the formulas and you want the formulas to convert to the values after converting the text format to general format, you will need to select each cell in turn and press F2 followed by Enter.

Thank you.

This has solved my problem. Actually, what I did is copy the entire contents of the initial worksheet into a separate worksheet formatted "general" and I was able to apply the formulas there.

My next problem is that (in using the CONCAT function to combine the text of different cells) the source data that must be combined is sometimes across one, sometimes across two, sometimes across three and sometimes across four different rows. What I am doing is copying the CONCAT formula (just using Ctrl+C and CTRL+V) for each situation (e.g. there are four CONCAT formulas - whether the data combined is either in two, three, four or five rows). However, this deals only with the data in one column.

I then have to match data in other columns from the source spreadsheet manually, so that everything I need is neatly in Columns A, B, C, D and E. I wish there was easier way, as I still have to manually delete the "empty rows" of data in the final spreadsheet. I am not allowed to use macros.

Thank you for your reply.

Unfortunately I cannot visualize what you have as the source data or what you require for the output. Therefore, to assist you further, I really need a copy of the source data plus a copy of the required output.

Does not need to be all of the data. Just copy a sample of the data to a new workbook

If the data contains sensitive data then replace the sensitive data with dummy data.

Then upload the sample workbook to OneDrive as follows.

Not sure if you need assistance to upload a file for sharing on OneDrive but in case you do, the following are guidelines to do so.

Note that sharing a link to a specific file on OneDrive does not give me access to any other files on your OneDrive and in fact I cannot even see the file list. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 6 to get the link but please zip the file before uploading.)

Sharing links to business OneDrive often does not work because the business has applied security measures that prevent this. Some people upload from their own private OneDrive.

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive for these purposes because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file. Do not use 3rd party compression applications because I cannot unzip them.
3. Go to this link.  https://onedrive.live.com Use the same login Id and Password that you use for this forum.
4. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
5. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
6. Right click the file name in OneDrive.
7. Select Share.
8. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
9. Click Copy button.
10. Change back to this forum and paste the link.

. . .

My next problem is that (in using the CONCAT function to combine the text of different cells) the source data that must be combined is sometimes across one, sometimes across two, sometimes across three and sometimes across four different rows. What I am doing is copying the CONCAT formula (just using Ctrl+C and CTRL+V) for each situation (e.g. there are four CONCAT formulas - whether the data combined is either in two, three, four or five rows). However, this deals only with the data in one column.

I then have to match data in other columns from the source spreadsheet manually, so that everything I need is neatly in Columns A, B, C, D and E. I wish there was easier way, as I still have to manually delete the "empty rows" of data in the final spreadsheet. I am not allowed to use macros . . .

To enable the Community to assist You, please share a sample workbook (via OneDrive, Dropbox, WeTransfer, etc.)

In the Workbook, 2 points should be clear:

- Your dataset

- The expected result

Thank You!