Increment numbers part of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I add automate quarter year headings?
Q12008
then add 1 to quarter until Q4 & then add 1 to year & restart at Q1 etc
to give:

Q22008
Q32008
Q42008
Q12009

Can anybody help?
 
="Q"&IF(MID(A1,2,1)="4",1,MID(A1,2,1)+1)&IF(MID(A1,2,1)="4",RIGHT(A1,4)+1,RIGHT(A1,4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Many thanks!

Bob Phillips said:
="Q"&IF(MID(A1,2,1)="4",1,MID(A1,2,1)+1)&IF(MID(A1,2,1)="4",RIGHT(A1,4)+1,RIGHT(A1,4))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
I'd insert a new worksheet (temporarily)
Put this in A1 and drag down:
="Q"&MOD(ROW()-1,4)+1&2008+INT((ROW()-1)/4)

Select what you want
edit|copy
go to where you want it
edit|Paste special|Values

You could even fiddle around with the formula and use it in place...

If I wanted Q12008 to be in X79, I'd use:
="Q"&MOD(ROW()-3,4)+1&1989+INT((ROW()-1)/4)
 
Back
Top