MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sorry! Adding a number to the front of a string...


Posted by Steven O'Neill on January 29, 2001 7:06 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 Mark W. on January 29, 2001 7:31 AM

Steven, assuming that your values are in column A,
insert a new column B as needed and use:

=IF(LEFT(A1)+0,0,"")&SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")

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

Cheers for that!

It worked!