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!
