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?
Microsoft Excel help needed!
-
- Sojourner
- Posts: 3923
- Joined: Fri Jul 26, 2002 5:01 am
- Location: Waterdeep
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!
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!
Birile wrote: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...
<a href="http://www.code-haven.com">Code Haven</a> - For all your programming needs.
I detest what you write, but I would give my life to make it possible for you to continue to write. - Some Guy Who Paraphrased Voltaire
I detest what you write, but I would give my life to make it possible for you to continue to write. - Some Guy Who Paraphrased Voltaire
-
- Sojourner
- Posts: 3923
- Joined: Fri Jul 26, 2002 5:01 am
- Location: Waterdeep
Birile wrote: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.
Birile wrote: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.
Teflor does. Teflor does not.
Return to “General Discussion Archive”
Who is online
Users browsing this forum: No registered users and 38 guests