Concatenation of Text

[Chris Hankin]

Hello, could someone please help me with a formula or macro to
concatenate columns Q and R and put the results into column S?

I am using Excel 2003 and have 4-digit numbers in column Q that are
formatted as Text. I also have 9-digit numbers in column R that are
formatted as Text. I need to concatenate the numbers so that they form
a 13-digit number in column S.

Once this is achieved, I need to format these 13-digit numbers in
####-##-###-#### format.

Example:

Column Q: 5830
Column R: 019640028

Column S: 5830019640028

After formatting: 5830-01-964-0028

The data starts at Q2 and R2 respectively.

Any help would be greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[OssieMac]

Hi Chris,

=Q2&"-"&LEFT(R2,2)&"-"&MID(R2,3,3)&"-"&RIGHT(R2,4)

 

[Rick Rothstein]

Use this formula in S1...

=--(Q1&R1)

and copy it down; format the column using this Custom Format...

0000-00-000-0000

 

[Chris Hankin]

Thanks OzzieMac - your formula works very well - greatly appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Chris Hankin]

Thanks Rick for your help - very much appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein]

For the archives, this is a little bit more compact...

=TEXT(--(Q2&R2),"0000-00-000-0000")

 

[Chris Hankin]

Thanks again Rick for your help - very much appreciated.

Kind regards,

Chris.



*** Sent via Developersdex http://www.developersdex.com ***