0

I am trying to merge two cells into one and know how to do this nice and easy, my problem is that numbers below 100 (ie 001) loose their "0"'s.

Example:

Cell A1 - "AAA" Cell B1 - "001" Cell C1 - =A1&"-"&B1

Cell C1 should show "AAA-001" but it only shows "AAA-1"

Cell B1 is formatted to be a number showing 001.

I have tried using CONCANTATE and TEXT but to no avail. Anyone know how I can make excel produce the result "AAA-001"?

2 Answers 2

5

The problem is that unless you tell it in some way excel will interpret the number as a number and will not look at the format you specified for the number. You can solve this in two ways:

1) Format B1 as Text

2) More elegant - use text formula to convert the number to text in format that you want:

 =A1&"-"&TEXT(B1,"000")

This will give the result you want and will preserve B1 as number that can be used for calculations. Text formula takes two arguments separated by comma, the first is the number, the second is the format you want. Say if you wanted to insert pound sign before value, and use decimal point, your format would be "£000.00"

1
  • Perfect. I knew what the problem was but just could not solve it, using "000" as the format for the text worked perfectly. TY so much, you just saved me maybe 20 hours work over the next few months :) Dec 12, 2013 at 13:16
0

Try to add a single quote in the cell in front of the 001 like this: '001.

or

if this is not possible, then I would use a VBA function (put it in a VBA module):

Public Function Padding(cell As Range, paddingFormat As String) As String
  Padding = format(cell.Value, paddingFormat)
End Function

Then you could use a formula like: =Concatenate(A1;"-";Padding(A2,"000")) - padding returns 001 even if A2 equals 1.

the paddingFormat is based on the Format function (see link below). In your case you need 000 to force 3 digits at minimum all the time. http://msdn.microsoft.com/en-us/library/59bz1f0h(v=vs.90).aspx

1
  • Unfortunatly the 001 is gathered from an outside sorce and copied into place as 1 therefor converting it to text using ' will not help be a massive time sink and I might as well leave it as 001 and compare the two by eye. Thank you for your answer though. Dec 12, 2013 at 12:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .