Zero in front of number

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hello
My problem is, I've been given a spreadsheet where one of the columns is
Mobile numbers and the person who filled it in I guess didn't know that you
can format the column to 'text ' to keep the Zero in front of the number, so
he put a # in front of each number in order to keep the first 0.
I now want to remove all the #. But it's a very long list.
So I've put in FIND #0 and REPLACE 0 but when I click REPLACE ALL, it takes
away the # but also all the first Zeros!!. I've tried formatting the column
as Text, General, Custom but it doesn't work.

BTW why does Excel not like having a Zero in front of a number particularly
when you specifically type it in? If you didn't want it, you wouldn't type
it. right?
Anyhow so PLEASE could someone help me on this ??
JB
 
Hi,

Try Replace '0
The ' marks it in a way that Excel uses it as text and will not supress
leading zeros.

Udo
 
Highlight the column and format it as Text before you do the Find and
Replace - you only need to Find #
and Replace it with nothing or "".

Alternatively, insert a new column next to the column of mobile phone
numbers, then insert this formula:

=RIGHT(D1,LEN(D1)-1)

assuming the numbers are in column D. Copy this down by double-clicking
the fill-handle (small black square in bottom right corner of the
cursor). Click <copy> (as the cells are already highlighted) and Edit |
Paste Special | Values (check) OK and <Esc>. Then you can delete the
old column D.

Hope this helps.

Pete
 
Thanks that worked!
I also found that in Replace there's an option button which then shows
format buttons. When I clicked the Format for Replace, chose Custom and
typed 0############ that worked too.
Your way was faster
Thanks
 
Back
Top