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

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

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" <(E-Mail Removed)> wrote in message
news:uWW%(E-Mail Removed)...
>
> 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 ***

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

Kind regards,

Chris.



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

Thanks Rick for your help - very much appreciated.

Kind regards,

Chris.



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

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

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

--
Rick (MVP - Excel)


"OssieMac" <(E-Mail Removed)> wrote in message
news:82E83249-0F04-47F9-AA63-(E-Mail Removed)...
> 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 ***
>>

Thanks again Rick for your help - very much appreciated.

Kind regards,

Chris.



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