Concatenation of Text
 

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 ***

Concatenation of Text
 

Hi Chris,

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

--
Regards,

OssieMac


"Chris Hankin" wrote:


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 ***

Concatenation of Text
 

Use this formula in S1...

=--(Q1&R1)

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

0000-00-000-0000

--
Rick (MVP - Excel)


"Chris Hankin" wrote in message
...

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 ***

Concatenation of Text
 

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

Kind regards,

Chris.



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

Concatenation of Text
 

Thanks Rick for your help - very much appreciated.

Kind regards,

Chris.



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

Concatenation of Text
 

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

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

--
Rick (MVP - Excel)


"OssieMac" wrote in message
...
Hi Chris,

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

--
Regards,

OssieMac


"Chris Hankin" wrote:


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 ***

Concatenation of Text
 

Thanks again Rick for your help - very much appreciated.

Kind regards,

Chris.



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