Problem With Concatenating Numbers

[Dazzawm]

I have some numbers in a 3 digit format e,g 001 002, 014, 088 etc.. and I am trying to add them onto the end of some other text/numbers by concatenation but the problem is it knocks of the zeros.

For example I have in a cell AR3313 and I want to add 001 to the end (with a space) so it looks like AR3313 001, but when I do it the result is AR3313 1. How can I rectify this please? Thanks

(In the cell it says 001 etc but in the formula bar it just says 1)

 

[Sektor]

Cell A1 (Text format): 001
Cell B1: AR3313
Cell C1 formula: =B1 & " " & A1

 

[BillXL]

Or you could try this. Assuming your number is in A1 (in number format) and the text is in B1. In C1 enter:

=CONCATENATE(TEXT(A1,"000"),B1)

The TEXT function changes the number to text in the specific format you want.

Bill

 

[Dazzawm]

Sektor with yours I change the 001 to text and it goes to 1 rather than 001 and I am getting the same problem as before and with yours Bill there is no space and a 1 rather than a 001.

 

[Tom Urtis]

=b1&" "&text(a1,"00#")

 

[Sektor]

1. Set Text format.
2. Press F2 and type in 001.
3. Press Enter.

 

[BillXL]

I forgot the space.

try this:

=CONCATENATE(TEXT(A1,"000")," ",B1)

 

[Dazzawm]

=b1&" "&text(a1,"00#")

Thanks everyone, Tom yours worked.