Leading zeroes

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
=+VALUE(A2&CHOOSE((5-LEN(B2)),"0","00","000","0000")&B2)



Meaux said:
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
 
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


Meaux said:
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
 
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)



Meaux said:
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
 
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.
 
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.



Meaux said:
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)
 
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...
 
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)


Meaux said:
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)
 
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
 
thanks again, it works great. I got seceral other responses and they were not close to working, good job
 
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.)


Meaux said:
thanks again, it works great. I got seceral other responses and they were
not close to working, good job
 
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
 
Back
Top