0
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?
Ross
Posted 2012-02-07T20:37:00.920
Reputation: 1
Concatenating # with leading 0's in Excel
Answers
1
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
barry houdini
Posted 2012-02-07T20:37:00.920
Reputation: 10 434
+1 for using the TEXT formatting function. In other words how ever many places your number "could" be add the "000000" zero's. – opsin – 2012-02-08T00:09:51.310
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 – 2012-02-07T20:49:24.163
1Welcome 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 – 2012-02-07T21:02:26.713