special formatting?

[jim sturtz]

i have a cell i wish to fill in from selections in various other cells and i
cant quite get it to work


a1 = the date 10/1/6
a2 = month(a1) this shows in a cell as 10
a3=day(a1) this shows in a cell as 1 (unless i put
special format of '00', then itll show as 01)
a4=year(a1) this shows in a cell as 2006 if the formatting
is text
a5=QI just plain text QI

i need to build a string from those to be QI061001

i can reference the right(xx,2) of all cells to get almost what i want with

=right(a5,2)&right(a4,2)&right(a2,2)&right(a3,2) except the very last
right(a3,2) keeps coming back a 1 w/o the leading 0. the same problem
arises with a month less than 10 also.

i need to pad all the numeric fields with 0 to a width of 2, so a plain old
1 would show as 01.

how can i do this?

thanks.

jim

microsoft.public.excel.worksheet.functions

 

[Guest]

=CONCATENATE(A5,RIGHT(A4,2),TEXT(A2,"00"),TEXT(A3,"00"))

 

[Guest]

=CONCATENATE(A5,RIGHT(A4,2),TEXT(A2,"00"),TEXT(A3,"00"))

 

[Bob Phillips]

=A5&TEXT(A1,"yymmdd")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

=A5&TEXT(A1,"yymmdd")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[jim sturtz]

thanks for the quick replies. the excel support groups always prove to be
great

 

[jim sturtz]

thanks for the quick replies. the excel support groups always prove to be
great