MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Adding a


Posted by Steven O'Neill on January 29, 2001 7:05 AM

I have a column of fax numbers some which do not start with a zero and others which contain a minus eg.

01753-852579
or
1753 852579

The correct format I need is -
01753852579

I need to have a zero at the front of each number and no spaces or dashes.

Is there a way of getting Excel to take each number and add a zero if one is missing and take away a space or a dash if it's there?

Cheers in advance,


Steven O'Neill


Posted by Carl on January 29, 2001 3:36 PM

Select the cells you want to format.

On the Format menu, click Cells, and then click the Number tab.

In the Category list, click Custom.

In the Type box, edit the number format codes to create the format you want.
Editing a built-in format does not remove the format.

To display Use this code
12 as 00012 and 123 as 00123 00000
12 as 00012 and 123 as 000123 "000"#
123 as 0123 "0"#
8.9 as 8.900 #.000
.631 as 0.6 0.#

Notes

You can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance.

You can also use the & (ampersand) text operator to join, or concatenate, two values to produce one continuous text value.