Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Can you provide a "before" and "after" example? I'm still not clear on what you would like to accomplish. – skypecakes Jan 18 '11 at 23:32
@studiohack, some feedback for you: As far as I can tell, your comment added no information to the exchange. Did I miss something? – skypecakes Jan 18 '11 at 23:54
up vote 3 down vote accepted

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!
share|improve this answer
+1, that covers the "prepend/append" part of my suggestion. – skypecakes Jan 18 '11 at 23:41

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... ;-)
End Sub
share|improve this answer

If you want the cells to contain only a T:

  1. Add a T to the first cell in the column.
  2. Copy that cell by selecting it and pressing ctrl-C.
  3. Now select the rest of the column: Press the down arrow once, then hold down the shift key and press Page Down until you get to row 10,000.
  4. Then press Ctrl-V to paste the copied cell into the selected cells.

If you want the cells to have a "T" prepended or appended to the existing content:

You will need to create a formula that references the original column, and paste it into a temporary column in a manner similar to the above. See Hellion's answer for how to do that.

share|improve this answer
Thank you for your answer.Maybe I wasn't clear - I meant when there's unique content in each cell (like names). – Anne Jan 18 '11 at 23:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .