Retain number values when changing formatting to text

I have a column of numbers I've converted to three digits by adding a leading zero. I need to add them to a column in a separate sheet using a concatenating formula. I have tried every which way to convert the column formatting to text (including using special pasting to create a new column) but the formatting always reverts to special. If I leave as is, the column won't concatenate properly on the new sheet (it drops the leading zero). Has anyone had this problem and found a solution? I wonder if it has to do with my version of excel because this wasn't a problem in the past. Thank you!

I presume you have real numbers where the leading zero was added by using Custom Formatting. You can concatenate this value into a text string using the TEXT function. So, if cell A1 contains your number, you can use this to concatenate the value with its leading zeroes...

TEXT(A1,"000")

That seems like the correct solution (thank you for that!), but I don't think I am working it into the formula correctly. Here is what I did:

=CONCATENATE("000000s",data!H2," cau",data!TEXT(A2,"000")," g o vl",data!B2," d")

The final product should look like this:

000000s2019 cau212 g o vleng d

(It's the code for a library record for a video recording in English that's 212 minutes long.

Originally Posted by daryne

=CONCATENATE("000000s",data!H2," cau",data!TEXT(A2,"000")," g o vl",data!B2," d")

What I highlighted in red is located incorrectly... it belongs attached to the A2 cell reference, not the function using it.

=CONCATENATE("000000s",data!H2," cau",TEXT(data!A2,"000")," g o vl",data!B2," d")

You are my absolute hero. Thank you so much. It worked beautifully.