Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to concatenate 3 fields together but some of the fields have leading 0's. Does anyone know of a way to use this formula and still be successful?

share|improve this question

closed as not a real question by studiohack Feb 8 '12 at 15:56

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What do you mean? You need to be a bit more specific in what you're trying to concatenate, why the leading zeros cause a problem, and what the end result needs to be. Why can't you change the format of all the cells to not show leading zeros if they are numbers? Are the string lengths variable? That type of information. –  Raystafarian Feb 7 '12 at 20:49
1  
Welcome to Superuser. In order for us to be able to help you, like @Raystafarian asked, can you provide more details? What have you tried so far? Can you post the formula you are trying or a screen shot? –  CharlieRB Feb 7 '12 at 21:02
add comment

1 Answer

You lose formatting when you concatenate so if, for example, A1 has the number 12 but is formatted to show 0012 then when you concatenate you lose the formatting, e.g. with "text" in B1

=A1&" "&B1 gives

12 text

If you want it to be 0012 text then use TEXT function in the concatenation, something like this

=TEXT(A1,"0000")&" "&B1

share|improve this answer
    
+1 for using the TEXT formatting function. In other words how ever many places your number "could" be add the "000000" zero's. –  opsin Feb 8 '12 at 0:09
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.