Zero in front of number

[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

 

[Udo]

Hi,

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

Udo

 

[Pete_UK]

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

 

[juan manuel]

yes

 

[JB]

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

 

[Pete_UK]

Thanks for feeding back to the group.

Pete