Excel Formula Question! Help!?

Question

Hi. So i have a spreadsheet for work. Each row has several cells full of data. All words, no numbers.

For example row 1 might have a different word of text in cells A1 - Z1. I want to use a formula (there must be one!!) that allows me to combine all the data inside the cells (of each row) into a single cell. I basically want to separate the data from each cell with a comma and form a list.

Is this easy to do>

Thanks!

Answer 1

The fastest way might be to simply highlight the entire row and copy it into memory.



Minimize Excel and open NotePad or TextPad or any text editor.



Paste the data into Notepad.



Add the 25 commas you need, to separate the words.



Copy this data into memory, minimize Notepad, swap back over to Excel and paste it in the cell you want.



Done. No fancy formula required.

Answer 2

I agree with Mr. Ed.

There is not an easy formula that will handle this (especially if you are looking at more than 5 or 6 cells in a row)



Your other option would be to use a macro to accomplish this



someone else could probably write something nicer, but if you inserted a new column A

select column A cell on the first row containing text

then run this and it will place all the combined text for each row in column A until it reaches a row with nothing in column B





Sub stringtogether()

Dim finalText As String

Do

For Each cell In Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1).End(xlToRight))

finalText = finalText & cell.Value & ","

Next

ActiveCell = Left(finalText, Len(finalText) - 1)

ActiveCell.Offset(1, 0).Select

finalText = ""

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub



hope that helps

Answer 3

Are the numbers to the nicely suited of the / character consistently one digit in length? if so, paste this formulation in D16 =SUMPRODUCT((N(suitable(A1:A1000, a million)=suitable(D15,a million)))*(B1:B1000)) in the experience that your records is going previous row 1000, exchange the thousands to three extensive type extra suitable than what you have. The above formulation assumes all your records in column A starts off with g/z.

Answer 4

Have you tried CONCATENATE, e.g. =CONCATENATE(A1,", ",B1,", ",C1) ?