Formatting Text Values to Remove Zeros
By default, Excel expects to work with numbers. When a workbook formula returns no value, Excel displays it as a zero. This default behavior works well in many cases, but it displays unwanted zeros for blank text fields.
You can cause Excel to format empty field values as blanks by appending "the empty string" to the end of your formula. To do this, select the top cell containing your formula and then click to the right of the text in the Formula bar and type <space> & <space> <double quote> <double quote> <Enter>.
Once you have edited the formula, double-click the fill handle to propagate the change to the rest of the formulas in the column.
Here is what the above example looks like after performing these steps: