How to concatenate 3064 and 01 with 306401 as a result?
 

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

To insert a "carriage return" when typing in a cell...
Hold down the [alt] key and press [enter]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Please ignore that [alt]+[enter] post...
it has nothing to do with THIS thread....Apologies.


***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

To insert a "carriage return" when typing in a cell...
Hold down the [alt] key and press [enter]

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Hi Ron,
I will try it on Monday and let you know. I really appreciate your help.

Thanks,
Lia
"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Assuming you don't need them as real numbers:-

=TEXT(A1,"0000")&TEXT(B1,"00")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"MSOChick" wrote in message
...
I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when
zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing
with
set criteria such as 000000 or ###### and It does not work. I am using
Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Hello,
Ok, it works for the 01, but not when the first number of the lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia


"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned when zero
as the first or last digit of the donor cell. I want the zero to remain in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing with
set criteria such as 000000 or ###### and It does not work. I am using Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

When the first number starts with a zero, such as 0430, then your result
cell will need to be formated as 000000, which was an option which you
mentioned in your OP.

The *100 is a way of saying "multiply by one hundred". Multiplying by one
hundred moves the digits two places to the left compared with the decimal
point.
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Ok, it works for the 01, but not when the first number of the lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia

"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned
when zero
as the first or last digit of the donor cell. I want the zero to remain
in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing
with
set criteria such as 000000 or ###### and It does not work. I am using
Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Hello,
Thanks for your help. It works fine when I do that, but when I
concatenate with another cell after that it still deletes the leading 0's.
I've tried fromatting the original cell with 430 as a text cell and it still
doesn't help. I've resorted to just adding the the extra 0's afterwards.

Thank you all for all of your help!

Regards,
Lia


"David Biddulph" wrote:

When the first number starts with a zero, such as 0430, then your result
cell will need to be formated as 000000, which was an option which you
mentioned in your OP.

The *100 is a way of saying "multiply by one hundred". Multiplying by one
hundred moves the digits two places to the left compared with the decimal
point.
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Ok, it works for the 01, but not when the first number of the lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia

"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned
when zero
as the first or last digit of the donor cell. I want the zero to remain
in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried to
concatenate using absolute value $, and ". I have also tried formatiing
with
set criteria such as 000000 or ###### and It does not work. I am using
Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

If you are saying that you want to turn Ron's answer into a text string that
you can do further concatenation with, then try
=TEXT(A4*100+B4,"000000")
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Thanks for your help. It works fine when I do that, but when I
concatenate with another cell after that it still deletes the leading 0's.
I've tried fromatting the original cell with 430 as a text cell and it
still
doesn't help. I've resorted to just adding the the extra 0's afterwards.

Thank you all for all of your help!

Regards,
Lia

"David Biddulph" wrote:

When the first number starts with a zero, such as 0430, then your result
cell will need to be formated as 000000, which was an option which you
mentioned in your OP.

The *100 is a way of saying "multiply by one hundred". Multiplying by
one
hundred moves the digits two places to the left compared with the decimal
point.
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Ok, it works for the 01, but not when the first number of the
lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia

"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned
when zero
as the first or last digit of the donor cell. I want the zero to
remain
in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried
to
concatenate using absolute value $, and ". I have also tried
formatiing
with
set criteria such as 000000 or ###### and It does not work. I am
using
Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Did you try the option I gave you?

Are your digits different lengths?

Regards
Ken..............

"MSOChick" wrote in message
...
Hello,
Thanks for your help. It works fine when I do that, but when I
concatenate with another cell after that it still deletes the leading 0's.
I've tried fromatting the original cell with 430 as a text cell and it
still
doesn't help. I've resorted to just adding the the extra 0's afterwards.

Thank you all for all of your help!

Regards,
Lia


"David Biddulph" wrote:

When the first number starts with a zero, such as 0430, then your result
cell will need to be formated as 000000, which was an option which you
mentioned in your OP.

The *100 is a way of saying "multiply by one hundred". Multiplying by
one
hundred moves the digits two places to the left compared with the decimal
point.
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Ok, it works for the 01, but not when the first number of the
lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia

"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned
when zero
as the first or last digit of the donor cell. I want the zero to
remain
in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried
to
concatenate using absolute value $, and ". I have also tried
formatiing
with
set criteria such as 000000 or ###### and It does not work. I am
using
Excel
2003. What am I doing wrong?

How to concatenate 3064 and 01 with 306401 as a result?
 

Thank you very much! That solved the problem! I got another array from a
co-worker, but this one was much easier!

Thanks again for everyones help!

Lia

"Ken Wright" wrote:

Did you try the option I gave you?

Are your digits different lengths?

Regards
Ken..............

"MSOChick" wrote in message
...
Hello,
Thanks for your help. It works fine when I do that, but when I
concatenate with another cell after that it still deletes the leading 0's.
I've tried fromatting the original cell with 430 as a text cell and it
still
doesn't help. I've resorted to just adding the the extra 0's afterwards.

Thank you all for all of your help!

Regards,
Lia


"David Biddulph" wrote:

When the first number starts with a zero, such as 0430, then your result
cell will need to be formated as 000000, which was an option which you
mentioned in your OP.

The *100 is a way of saying "multiply by one hundred". Multiplying by
one
hundred moves the digits two places to the left compared with the decimal
point.
--
David Biddulph

"MSOChick" wrote in message
...
Hello,
Ok, it works for the 01, but not when the first number of the
lead
cell is a 0, such as 0430. By the way, what does the *100 stand for?

Thanks,
Lia

"Ron Coderre" wrote:

Try something like this:

A4: 3064
B4: either 01 (as text) or 1 (formated as 00)

C4: =A4*100+B4

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"MSOChick" wrote:

I would like to be able to combine cells with 4 digits and 2 digits
respectively into a cell that results in the entire number returned
when zero
as the first or last digit of the donor cell. I want the zero to
remain
in
place. Such as 3064 in A4 and 01 in B4 becoming 306401. I have tried
to
concatenate using absolute value $, and ". I have also tried
formatiing
with
set criteria such as 000000 or ###### and It does not work. I am
using
Excel
2003. What am I doing wrong?