Concatenate without losing leading zeroes?

[paquirl]

Column A has numbers that should all be 6 digits. Some have leading zero(es).
Column B has numbers that should all be 5 digits. Some have leading zero(es).
Column C should combine both A and B with every cell having an 11 digit number.

 

[Joe4]

Try:

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

for an entry in row 1.

 

[paquirl]

I used as a formula in C1 and it is giving me an invalid name error. Says the formula contains unrecognizable text.

 

[Joe4]

I used as a formula in C1 and it is giving me an invalid name error. Says the formula contains unrecognizable text.

The TEXT function has been around forever in Excel, so that should not cause any issues.

Which version of Excel are you using? If a European version, you may need to use semi-colons instead of commas, i.e.

=TEXT(A1;"000000") & TEXT(B1;"00000")

You aren't doing this in Google Sheets, by chance, are you?

 

[paquirl]

Using 2016, standard US version , no Google sheets.

 

[Joe4]

Using 2016, standard US version , no Google sheets.

There is nothing in that formula that should cause any issues.
Did you copy and paste it in directly, or did your try to re-type it yourself?

What exactly is in cells A1 and B1 (please post examples)?
Are they hard-coded, or are they the result of formulas?
If formulas, please post those formulas.

 

[paquirl]

ok i see something odd, this particular spreadsheet (sent to me by a colleague) has numbers for column headers instead of the letters.

 

[Fluff]

Sounds like it's using R1C1 notation, in which case try

=TEXT(RC[-2],"000000") & TEXT(RC[-1],"00000")

 

[Joe4]

ok i see something odd, this particular spreadsheet (sent to me by a colleague) has numbers for column headers instead of the letters.

Ah yes.
The devil is often in the important details left out!

 

[paquirl]

it wont let me select both formulas as the solution to this thread