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

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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


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

XL2002, WinXP

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

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?

 

[Guest]

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

Thanks,
Lia

 

[Ken Wright]

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

 

[Guest]

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

 

[David Biddulph]

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.

 

[Guest]

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]

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")

 

[Ken Wright]

Did you try the option I gave you?

Are your digits different lengths?

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

 

[Guest]

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