How do I add content to Excel cells?

Question

How can I add a T to the content of each cell in a certain column (that contains 10000 cells) without typing it manually?

Solution 1

The non-programming way:

1. insert a new column, I'll call it B, next to your data column, which I'll call A.
2. set the value in cell 1 of the column to: =A1 & "T" (this appends a "T" to the end of the cell, if you need it at the beginning you can write ="T" & A1)
3. grab the cell-extender box thingy (sorry, I can't think of its proper name) at the bottom-right corner of the highlighted cell) and drag down 10,000 cells, and then let go. Your formula is replicated all the way down, with cell references automatically adjusted.
4. If you really want the 'T'-added values in the original column, copy column B, then right-click on the column A header and choose "Paste Special...", choose "Values", and click OK. Then delete column B. Warning: if column A uses formulas to display its data, this will destroy those underlying formulas, leaving only the values!

Solution 2

Here's some VBA code that you can modify to fit your needs. If you run it as is, it will add the letter "T" to the end of whatever text value is in cells A1 through A10.

Have fun.

Sub ConcatString()

Dim myCell As Variant, myRange As Range, myString As String
Set myRange = Range("A1:A10") 'whatever range you want...
myString = "T" 'whatever string you want...

For Each myCell In myRange
    myCell.NumberFormat = "Text" 'convert values to text for string concat...
    myCell.Value = myCell.Value & myString 'add string value to cell value...
    'reformat cells if you want following above example...
    'removing the added string is up to you... ;-)
Next
End Sub

Comments

[stone]

Can you provide a "before" and "after" example? I'm still not clear on what you would like to accomplish.

[stone]

@studiohack, some feedback for you: As far as I can tell, your comment added no information to the exchange. Did I miss something?

[Anne]

Thank you for your answer.Maybe I wasn't clear - I meant when there's unique content in each cell (like names).

[stone]

+1, that covers the "prepend/append" part of my suggestion.