• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula that increments text: week 1, week 2...

J

jblack

Member
I am looking for a formula that will allow me to put "Week 1" in a column header cell so that the other column header cells will be "Week 2", "Week 3", etc.....

Thanks
 
Hi,

Please insert some formula in A1 cell like below. Copy and paste the same formula into B1:D1 range.
Formula = "Week - "&COLUMN()

Thanks,
Mahesh
 
Good day jblack

You can not it with out formulas, just fill A1,B1,and C1 with the Week 1, Week 2 and Week 3 then select the three cells and drag right the fill handle as far as you want to go.
 
Thanks Mahesh, but what I am trying to do is in cell A1 I enter Week 1 and in cell B1 I entered =A1+1 expecting to get Week 2 but got #Value!
 
Okay, type "Week 1" in cell A1.
Then insert following formula in B1 as "=LEFT(A1,5)&COLUMN()"
1. Formula LEFT(A1,5) gives value as "Week ". What it does is, it returns first 5 characters from Left side value of A1 cell .
2. COLUMN() formula gives value as 2.
When formula adds both with Concatenate formula, result will be "Week 2" ("Week " & "2")

To know more about the excel formulas in detail, you can search in chandoo.org
 
If the header texts are for display only, you can also use a custom cell format like the following
"Week " #

Then you can use a simple formula like =A1+1 to increment.
 
Update.

In cell R1, I entered Week 1.
In cell S1 I entered =LEFT(R1,5)&RIGHT(R1,1)+1.
I then copied the formula in cell S1 and pasted it into cells T1 and U1.
The end result was that in Cell R1 thru Cell U1 I got Week 1, Week 2, Week 3 and Week 4.

Thanks to everyone for your help.
 
jblack said:
Update.

In cell R1, I entered Week 1.
In cell S1 I entered =LEFT(R1,5)&RIGHT(R1,1)+1.
I then copied the formula in cell S1 and pasted it into cells T1 and U1.
The end result was that in Cell R1 thru Cell U1 I got Week 1, Week 2, Week 3 and Week 4.

Thanks to everyone for your help.
Click to expand...

"I used the above formula and it worked perfectly until Weeks 11 and 12. These returned "Week 1" and "Week 2".

My cell range is B3 to M3.

In B3 I entered Week 1.
In cell C4, I entered =LEFT(B3,5)&IGHT(B3,1)+1. I then autofilled E4 to M4. I have checked the formula in L4 and M4 but cannot see what is causing them to return a value of Week 1 and Week 2.

Any suggestions?
 
You must log in or register to reply here.
Back
Top