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" <badspam@awyway.com> escribió en el mensaje
news:OpuQ2#UPGHA.2816@TK2MSFTNGP15.phx.gbl...
> 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
>
>

[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


"Udo" <udo.baumfalk@arcor.de> wrote in message
news:1141231993.275391.306640@u72g2000cwu.googlegroups.com...
> 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]

Thanks for feeding back to the group.

Pete