Concatenate Text, Number and Date

[Interloper]

Hi

I have a workbook that runs into several pages and I need to combine two
pieces of information from different worksheets into one cell to give the
user a reference number for making VAT payments.

The Reference Number Cell needs to combine text with a number and a date in
the format 'mm yy'.

The text is straightforward: "BACS VAT Payment Reference: "

The number is a bit trickier as it is taken from a cell formatted as 'Text'
where it may (or may not) have been entered with spaces (e.g. 1234 5678 90).
The number needs to appear in the Reference Number Cell without any spaces
(e.g. 1234567890). I thought the 'TRIM' or 'CLEAN' functions might remove
any spaces from the number, but they don't appear to.

The date is taken from a date cell formatted as 'mmmm yyyy', so it appears
as 'month year' (e.g. August 2011). I need it to appear in the Reference
Number Cell as 'mm yy' and to retain the leading zero for months 01 to 09
(e.g. 08 11 for August 2011, 12 11 for December 2011, etc.).

Assume:

Text is: "BACS VAT Payment Reference: "

The Number cell contains: '1234 5678 90'

The Date cell contains 'August 2011'

I would like the Reference Number Cell to read: "BACS VAT Payment
Reference: 1234567890 08 11"

Is this easily achieved?

Thanks,

 

[Claus Busch]

Hi,

Am Tue, 1 Nov 2011 21:58:23 -0000 schrieb Interloper:

Text is: "BACS VAT Payment Reference: "

The Number cell contains: '1234 5678 90'

The Date cell contains 'August 2011'

I would like the Reference Number Cell to read: "BACS VAT Payment
Reference: 1234567890 08 11"

text in A1, number in B1 and date in C1. Then:
=A1&SUBSTITUTE(B1," ",)&" "&TEXT(C1,"MM yy")


Regards
Claus Busch

 

[Interloper]

text in A1, number in B1 and date in C1. Then:
=A1&SUBSTITUTE(B1," ",)&" "&TEXT(C1,"MM yy")

Thanks Claus, your formula works perfectly.

It's so easy when you know how!

Regards,