up vote 0 down vote favorite
share [g+] share [fb]

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?

link|improve this 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 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 at 21:02
feedback

closed as not a real question by studiohack♦ Feb 8 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. See the FAQ for guidance on how to improve it.

1 Answer

up vote 1 down vote

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

link|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 at 0:09
feedback

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