I need the following text displayed in column A, as follows. It just needs to be plain text.
Cell A1: "R1" {without quoutes}
Cell A2: "R2" {without quoutes}
Cell A3: "R3" {without quoutes}and so on, for thousands of cells.
How can I achieve this?
24 Answers
If I understand your question correctly, you will want to put the following formula in cell A1:
=CONCATENATE("R";ROW(A1))This should place R1 in cell A1.
Now copy this cell, make a selection of all cells that should have this and paste.
If for example you select cells A2 to A99, Cell A87 will have the text R87 in it.
The formula explained:
=CONCATENATE( concatenate joins text and formulas together "R" start with the string R ; function separator ROW(A1) row(a1) returns the row number of cell a1. When copy/pasting this formula, A1 is updated to a new value relative to the original position. Since the formula itself is in A1, the new cell will have its cell in that formula automatically. So if you paste this in G19, the function becomes ROW(G19) ) This is the end bracket for the formula.There are a number of options, this would be my preference:
Type the following formula in cell A1
="R"&TEXT(ROW(A1),"#")If you want leading zeros so the number "looks nicer" use the following instead:
="R"&TEXT(ROW(A1),"0000#")or use as many leading zeros as you need, depending on the maximum value you expect to get to.
Then,
Select the cell. Click and drag on the handle at bottom right of the cell as far down as you want the formula to copy. Alternatively copy the cell, select all the cells you want to copy it to and paste.
You can also use
=CONCATENATE ("R",(ROW(A1))if you don't like using the TEXT function
Alternatively you can just type R1 in cell A1, R2 in cell A2, select both cells together and drag the handle at the bottom right corner of the selection down as far as you want to go. Excel will automatically add one to the previous number every time it copies a further cell down
If you need / want the numbering to differ from the excel ROW-number:
= "R" & ROWS($A$50:A50) in e.g. A50 - to have "R1" in A50, then enter and pull (copy) down.
The same can be used with COLUMNS()
And as always, TEXT(number,"formatcode") can be used for display formatting the number.
If you still want to be able to do maths on those numbers, consider using a custom number format... or the currency formatting for the South African Rand 8)
