Microsoft Excel help needed!

[kwirl]

Ok, this is something that i'm hoping one of you can help me with. I have a large Excel table, and i need to concatenate the comment values for each cell.

i.e. left to right, top row - comment value for first cell =1, comment value for second cell =2, etc etc

the important thing is that i need this to JUST happen to the comment values! anyone?

[teflor the ranger]

By concatenate, do you mean to join all of your comments together into one comment?

[Birile]

That's what concatenate means, joining comments from different cells together.

Try the following:

In whatever box you plan on concatenating all of the data you can use the following calculation:

Let's say you want to join the text in A1, B1 and C1 together into D1

In D1 type: =A1&B1&C1

All of the data in A1, B1 and C1 will show up together in D1. Be sure you've already put whatever cells you need into each of the cells you are concatenating, the concatenation will not do that for you (ie. put a space after the last word in each cell).

If you want to do this for several different rows (ie. if you want D2 to reflect A2, B2 and C2, etc.) all you have to do is copy D1, select as many cells below D1 as you want to continue the similar formula and Paste the formula. Each cell will handle the same formula for the corresponding fields within the formula. So, D17 will concatenate A17, B17 and C17 and will NOT concatenate A1, B1 and C1 erroneously.

Let me know if this helps!

Edit: Also, if you want your spreadsheet to just show what's in Column D (or whatever column you're using to concatenate everything), you can hide the columns used in the formula.

Conversely, you can take the data in one sheet and concatenate it into a separate sheet. When you open a new spreadsheet in Excel, it has 3 different sheets attached to it, indicated by small tabs at the bottom of the spreadsheet labeled "Sheet1", "Sheet2" and "Sheet3". If you want to concatenate from Sheet1 to Sheet2, do the same as I indicated above, except make the following change to the formula when you type it into Sheet2:

=Sheet1!A1&Sheet1!B1&Sheet1!C1

That sort of thing will clean up your spreadsheet nicely. The copy and paste function will make this all happen automatically--all you have to do is one simple copy and paste and all of the cells you need concatenated will be concatenated into the cells you want them to be (I'm assuming that's not too much work for you!).

Again, hope this helps!

[Sarvis]

That's what concatenate means, joining comments from different cells together.

Try the following:

In whatever box you plan on concatenating all of the data you can use the following calculation:

Let's say you want to join the text in A1, B1 and C1 together into D1

In D1 type: =A1&B1&C1

All of the data in A1, B1 and C1 will show up together in D1. Be sure you've already put whatever cells you need into each of the cells you are concatenating, the concatenation will not do that for you (ie. put a space after the last word in each cell).

Actually you could do A1 & " " & B1 " " & C1 instead to get spaces in between. Much cleaner, and you don't have to go back and change everything if you didn't do it before hand.

However, his question was about concatenating the COMMENT values of each cell... not just the cells themselves, so I have no idea how you do that. I'll try to look it up later...

[Ghimok]

I'm only an above-average Excel user, but I don't know of any way at all to manipulate the comment values on a cell through formulas, much less concatenate them.

[teflor the ranger]

That's what concatenate means, joining comments from different cells together.

Just because the user says concatenate, does not mean the user is actually trying to do so.

In D1 type: =A1&B1&C1

That would manipulate the cell value, not the comment value of the cell.


Moving right along:


Kwirl: I wrote a little script for you to convert your comments values into regular cell values. Not sure what you're trying to do, but it will make your comments much easier to manipulate. Make a copy of your workbook (excel file), open the copy, goto Tools>Macro>Visual Basic Editor, then right click on oh.... Microsoft Excel Objects (on the upper left) and insert a module.

Copy this code into the window that comes up:

Code: Select all

Sub Comment2Value()

  Dim myComments As Range
  Dim myCell As Range
  Dim myWorksheet As Worksheet
 
  Set myWorksheet = ActiveSheet

  On Error Resume Next
  Set myComments = myWorksheet.Cells.SpecialCells(xlCellTypeComments)
  On Error GoTo 0

  If myComments Is Nothing Then
     Exit Sub
  End If

  For Each myCell In myComments
      myCell.Value = myCell.Comment.Text
  Next myCell
 
End Sub

Go ahead and close the visual basic window (safe first if you want to). In order to run the script, you'll need to be back in your excel window. Again: Tools>Macro> then Macro. You'll see the Comments2Values highlighted. Hit run.


Now all the cells in your sheet that had comments should have their contents replaced by their comments.

If you can describe what you need a little better I might be able to help you out.