Help with a formula to string cells together

[kewwyn7]

Hi there,
Heres my data:
Name: Joe Bloggs (Cell ref - D3)
Date: 8/02/08 (Cell ref - L3)
Rev #: B (Cell ref - R3(not always populated))

And this is what I need to do:
BLOGGSJ080208B

Cheers to anyone of you geniuses that can help me.
K

 

[DonkeyOte]

Assuming your naming convention is always the same FORENAME space SURNAME then try.

=UPPER(RIGHT(D3,LEN(D3)-FIND(" ",D3))&LEFT(D3,1))&TEXT(E3,"DDMMYY")&R3

Note: I'm unsure as to your date format (date provided is ambiguous could be US/UK), I've assumed UK format but adjust DDMMYY to whichever format you require (MMDDYY).

HTH

 

[kewwyn7]

Hi Lasw10
Thanks for that ... it works brilliantly but on testing I entered the date 08/08/08 and it returned <TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><TBODY><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" width=128 height=29>GILLANK000100B</TD></TR></TBODY></TABLE>
Any ideas?
I am in Australia hence the date format.

Thanks,
K

 

[DonkeyOte]

Works for me -- are you sure you're referencing the correct cell ?
The value returned in your result is default Jan 1900 (assuming its MMDDYY format) which is what you'd get if you were referencing a blank cell or a cell with a value of < 1.

 

[kewwyn7]

OMG! That was it, sorry for the stupid question!
Thanks again,
K