I’m working with text values and numbers that need to be combined into one cell. Here’s my situation:
I have the word “Server” in cells A1 through A10, and in column B (B1-B10) I have numbers from 001 to 010. These numbers are formatted using custom format “000” to show leading zeros.
When I try to merge them in column C, I get results like Server1, Server2, etc. But what I actually want is Server001, Server002, and so on.
I already tried changing column C to text format but got the same problem. I need the proper formatting because I want to sort these values later. How can I keep the zero padding when combining the text and numbers?
This happens because Google Sheets strips custom formatting when you concatenate values. Use the TEXT function to fix it - it preserves number formatting during concatenation. Try =A1&TEXT(B1,"000") in column C. This keeps the three-digit format with leading zeros. I’ve done this tons of times for asset tags and item codes at work. The TEXT function converts your number back to the formatted string you want, so you’ll get Server001, Server002, etc. Copy the formula down column C and you’re good to go.
Had the exact same issue when creating inventory codes for our department. The problem is that concatenation treats numbers as actual values, not their displayed format. Another approach that works well is using the PRINTF function: =PRINTF("%s%03d", A1, B1). The %03d part ensures three digits with leading zeros. I find this method useful when dealing with more complex formatting since you can control the output format more precisely. Both this and the TEXT function approach will maintain proper sorting order - the zero padding ensures alphabetical sorting matches numerical order.
totally agree! text function works gr8. you can also use concatenate like =CONCATENATE(A1,TEXT(B1,"000")). just make sure to keep those quotes or the formatting breaks. both methods give you leading zeros!