Using the Concatenate function with both Alpha Characters and Leading Zeros

Question

I am trying to concatenate an alpha character with a numeric value that has leading zeros.  For example: 

Alpha Char  = W   

Numeric Value = 01209 (Cell = A2) 

When I use the =Concatenate("W", A2) the result that I get is W1209.  Then I tried to take the value W1209 (B2) and use the text function =Text(B2,000000) to get the result of W01209.  Doesn't work.  Any suggestions?  

The end result that I want is W01209.  How can you accomplish this in Excel? 

Thank you in advance.

Answer 1

As the leading zero is not appearing when CONCATENATE is used I belive we can assume A2 contains a number and a custom number format of 00000 is applied.

Therefore you need to apply that same custom number format within the concatenation.

=CONCATENATE("W",TEXT(A2,"00000"))

Answer 2

Hi Jason,

Thanks for the help.  This helped and continued research found an additional solition.  You can also combine the LEN and REPLACEMENT functions

example:  working with the following two values:

Q2 = W1290

Q3 = W12560

by using the the formula:

=IF(LEN(Q2)=5,REPLACE(Q2,2,0,0),Q2)

Q2 = W01290

Q3 = W12560

Answer 3

What is actually entered in cell A2 when you look at it in the formula bar?

What is the cell format of cell A2?

If the value in the formula bar is 1209, then CONCATENATE("W", A2) will result in W1209.

However, if the value in the formula bar when you are in cell A2 is 01209, then CONCATENATE("W", A2) will result in W01209.

If it is 1209, change the cell format to Text, and then retype the value 01209 in the cell. The function should then produce the result you want.