Leading zeroes

[Guest]

I have two cells I want to concatenate and keep the leading zeroes of the second cell. (e.g., first cell is 201, second cell is 245 which has been formatted as "00000" to show leading zeroes and thus shows as 00245, I want to concatenate these to show as 20100245) It is only showing the original second cell when I concatenate and thus shows as 201245

 

[Dave R.]

=+VALUE(A2&CHOOSE((5-LEN(B2)),"0","00","000","0000")&B2)

I have two cells I want to concatenate and keep the leading zeroes of the

second cell. (e.g., first cell is 201, second cell is 245 which has been
formatted as "00000" to show leading zeroes and thus shows as 00245, I want
to concatenate these to show as 20100245) It is only showing the original
second cell when I concatenate and thus shows as 201245

 

[Peo Sjoblom]

If it's always five digits for display

=--(A1&TEXT(B1,"00000"))

if it is always 2 leading zeros

=--(A1&TEXT(B1,REPT("0",LEN(B1)+2)))

--

Regards,

Peo Sjoblom

I have two cells I want to concatenate and keep the leading zeroes of the

second cell. (e.g., first cell is 201, second cell is 245 which has been
formatted as "00000" to show leading zeroes and thus shows as 00245, I want
to concatenate these to show as 20100245) It is only showing the original
second cell when I concatenate and thus shows as 201245

 

[Guest]

U R a genious, thank you, thank you. You just saved my 100 hours of work.

----- Dave R. wrote: -----

=+VALUE(A2&CHOOSE((5-LEN(B2)),"0","00","000","0000")&B2)

I have two cells I want to concatenate and keep the leading zeroes of the

second cell. (e.g., first cell is 201, second cell is 245 which has been
formatted as "00000" to show leading zeroes and thus shows as 00245, I want
to concatenate these to show as 20100245) It is only showing the original
second cell when I concatenate and thus shows as 201245

 

[Guest]

Thank you again, there was an error in value that occurred. When there were NO leading serious it returned the error "#VALUE!". I corrected the formula to =+VALUE(A2&CHOOSE((6-LEN(B2)),"","0","00","000","0000")&B2)

Thank you again.

 

[Dave R.]

Hi, is there still a problem? I should have stated that it will work if
there were 5 or fewer digits in column B.

I don't think just changing it to 6 would fix it, did it? Then it will make
22 into 000022 instead of 00022 which I thought you wanted (5 digit
numbers).

Glad to help.

Thank you again, there was an error in value that occurred. When there

were NO leading serious it returned the error "#VALUE!". I corrected the
formula to =+VALUE(A2&CHOOSE((6-LEN(B2)),"","0","00","000","0000")&B2)

 

[Dave R.]

Sorry I mean fewer than 5, so that you'd always end up with a 5 digit number
there, but I thought having 5 zeros (if b2 is blank) was unncessary...

 

[Dave R.]

Yipes.. to much round and round. this should do it for 5 digits and fewer.
If it's 5 digits long (b2), it just brings b2 over adding a "" to it which
is actually nothing.

=+VALUE(A2&CHOOSE((5-(LEN(B2)-1)),"","0","00","000","0000","00000")&B2)

Thank you again, there was an error in value that occurred. When there

were NO leading serious it returned the error "#VALUE!". I corrected the
formula to =+VALUE(A2&CHOOSE((6-LEN(B2)),"","0","00","000","0000")&B2)

 

[Ron Rosenfeld]

I have two cells I want to concatenate and keep the leading zeroes of the second cell. (e.g., first cell is 201, second cell is 245 which has been formatted as "00000" to show leading zeroes and thus shows as 00245, I want to concatenate these to show as 20100245) It is only showing the original second cell when I concatenate and thus shows as 201245

=A1&TEXT(B1,"00000")


--ron

 

[Guest]

thanks again, it works great. I got seceral other responses and they were not close to working, good job

 

[Dave R.]

Thanks. I think I like Ron's best though!! except that his changes the
numbers to text (don't know if thats a problem anyways, but you can always
just surround Ron's formula with VALUE(formula) and get a number.)

thanks again, it works great. I got seceral other responses and they were

not close to working, good job

 

[Ron Rosenfeld]

Thanks. I think I like Ron's best though!! except that his changes the
numbers to text (don't know if thats a problem anyways, but you can always
just surround Ron's formula with VALUE(formula) and get a number.)

Most math functions should coerce it. You can also precede it by a double
unary if that doesn't work.


--ron