Hi Everyone,

In Column A:A I have phone numbers in each cell and they are in this
format [Special -> Dutch -> Telefoonnummer]
e.g. 0290099009

If I make it into General '0' infront will disappear.
i.e. 290099009

Q: How do I change those numbers into 'Number stored in Text' (Those
numbers where little green box thingy appear on the top left of the
cell)? If I just change the format, it becomes 290099009 again and I
have to type '0' manually then it becomes 'Number stored as Text'.
Since I have like 40,000 numbers I don't think I can do it by
manaully.

thank you for your help.

What if you just format them as "text"

On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309 <jaedong1221@gmail.com>
wrote:

>Hi Everyone,
>
>In Column A:A I have phone numbers in each cell and they are in this
>format [Special -> Dutch -> Telefoonnummer]
>e.g. 0290099009
>
>If I make it into General '0' infront will disappear.
>i.e. 290099009
>
>Q: How do I change those numbers into 'Number stored in Text' (Those
>numbers where little green box thingy appear on the top left of the
>cell)? If I just change the format, it becomes 290099009 again and I
>have to type '0' manually then it becomes 'Number stored as Text'.
>Since I have like 40,000 numbers I don't think I can do it by
>manaully.
>
>thank you for your help.
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

On Jul 7, 4:40*pm, Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote:
> What if you just format them as "text"
>
> On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309 <jaedong1...@gmail.com>
> wrote:
>
>
>
>
>
> >Hi Everyone,
>
> >In Column A:A I have phone numbers in each cell and they are in this
> >format [Special -> Dutch -> Telefoonnummer]
> >e.g. 0290099009
>
> >If I make it into General '0' infront will disappear.
> >i.e. 290099009
>
> >Q: How do I change those numbers into 'Number stored in Text' (Those
> >numbers where little green box thingy appear on the top left of the
> >cell)? If I just change the format, it becomes 290099009 again and I
> >have to type '0' manually then it becomes 'Number stored as Text'.
> >Since I have like 40,000 numbers I don't think I can do it by
> >manaully.
>
> >thank you for your help.
>
> --
> Dave Mills
> There are 10 type of people, those that understand binary and those that don't.- Hide quoted text -
>
> - Show quoted text -

It won't work for me because even if I format them as 'text'
1. '0' disappears
2. I need to double click the cell in order to make the cell "Number
stored as Text"

James8309 <jaedong1221@gmail.com> wrote in
news:8ca05431-9e81-4c2c-b197-5d1e5891cbfb@t54g2000hsg.googlegroups.com:

> On Jul 7, 4:40*pm, Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote:
>> What if you just format them as "text"
>>
>> On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309
>> <jaedong1...@gmail.com
>>
>> wrote:
>>
>>
>>
>>
>>
>> >Hi Everyone,
>>
>> >In Column A:A I have phone numbers in each cell and they are in this
>> >format [Special -> Dutch -> Telefoonnummer]
>> >e.g. 0290099009
>>
>> >If I make it into General '0' infront will disappear.
>> >i.e. 290099009
>>
>> >Q: How do I change those numbers into 'Number stored in Text' (Those
>> >numbers where little green box thingy appear on the top left of the
>> >cell)? If I just change the format, it becomes 290099009 again and I
>> >have to type '0' manually then it becomes 'Number stored as Text'.
>> >Since I have like 40,000 numbers I don't think I can do it by
>> >manaully.
>>
>> >thank you for your help.
>>
>> --
>> Dave Mills
>> There are 10 type of people, those that understand binary and those
>> that
> don't.- Hide quoted text -
>>
>> - Show quoted text -
>
> It won't work for me because even if I format them as 'text'
> 1. '0' disappears
> 2. I need to double click the cell in order to make the cell "Number
> stored as Text"

all you need to do is show the 1st 0?
try this
- right click on a cell
- click format cells
- click custom
- in the type box...type in 0000000000
- then copy&paste the format down the column

excel will still not see the 1st 0, but at least it will show it :)
hope that works for you.

Hi Dave,

> - in the type box...type in 0000000000
That doesn't work with different lengths of the telephone numbers. It
could result in multiple leading zeros.

But you could insert a - ' - (single quotation mark) at the beginning
of the cell. In the cell next to it you insert the formula: = "'" & A1
(take care on the numbers of quotation mark, that is: double quotation
mark - single quotation mark - double quotation mark)

And then you copy it other the original cell with paste-special "value
only"

This way your phone numbers are text by definition and the formatting
should not be of a problem anymore.

If necessary you can use a makro to insert the single quotation mark.

Hope that helps
Harald Battran

If the phone numbers have already been converted into real numbers,
then in a helper column you can use this formula:

="0"&A1

and then copy down. If you fix the values (<copy> the helper cells,
then Edit | Paste Special | Values | OK then <Esc>), you can then copy
the helper column to over-write the original values in column A, and
then delete the helper column.

Hope this helps.

Pete

On Jul 7, 7:46*am, James8309 <jaedong1...@gmail.com> wrote:
> On Jul 7, 4:40*pm, Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote:
>
>
>
>
>
> > What if you just format them as "text"
>
> > On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309 <jaedong1...@gmail.com>
> > wrote:
>
> > >Hi Everyone,
>
> > >In Column A:A I have phone numbers in each cell and they are in this
> > >format [Special -> Dutch -> Telefoonnummer]
> > >e.g. 0290099009
>
> > >If I make it into General '0' infront will disappear.
> > >i.e. 290099009
>
> > >Q: How do I change those numbers into 'Number stored in Text' (Those
> > >numbers where little green box thingy appear on the top left of the
> > >cell)? If I just change the format, it becomes 290099009 again and I
> > >have to type '0' manually then it becomes 'Number stored as Text'.
> > >Since I have like 40,000 numbers I don't think I can do it by
> > >manaully.
>
> > >thank you for your help.
>
> > --
> > Dave Mills
> > There are 10 type of people, those that understand binary and those that don't.- Hide quoted text -
>
> > - Show quoted text -
>
> It won't work for me because even if I format them as 'text'
> 1. '0' disappears
> 2. I need to double click the cell in order to make the cell "Number
> stored as Text"- Hide quoted text -
>
> - Show quoted text -

Wont work with 2 or more leading zeros though. e.g. international numbers


On Mon, 7 Jul 2008 04:49:40 -0700 (PDT), Pete_UK <pashurst@auditel.net> wrote:

>If the phone numbers have already been converted into real numbers,
>then in a helper column you can use this formula:
>
>="0"&A1
>
>and then copy down. If you fix the values (<copy> the helper cells,
>then Edit | Paste Special | Values | OK then <Esc>), you can then copy
>the helper column to over-write the original values in column A, and
>then delete the helper column.
>
>Hope this helps.
>
>Pete
>
>On Jul 7, 7:46*am, James8309 <jaedong1...@gmail.com> wrote:
>> On Jul 7, 4:40*pm, Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote:
>>
>>
>>
>>
>>
>> > What if you just format them as "text"
>>
>> > On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309 <jaedong1...@gmail.com>
>> > wrote:
>>
>> > >Hi Everyone,
>>
>> > >In Column A:A I have phone numbers in each cell and they are in this
>> > >format [Special -> Dutch -> Telefoonnummer]
>> > >e.g. 0290099009
>>
>> > >If I make it into General '0' infront will disappear.
>> > >i.e. 290099009
>>
>> > >Q: How do I change those numbers into 'Number stored in Text' (Those
>> > >numbers where little green box thingy appear on the top left of the
>> > >cell)? If I just change the format, it becomes 290099009 again and I
>> > >have to type '0' manually then it becomes 'Number stored as Text'.
>> > >Since I have like 40,000 numbers I don't think I can do it by
>> > >manaully.
>>
>> > >thank you for your help.
>>
>> > --
>> > Dave Mills
>> > There are 10 type of people, those that understand binary and those that don't.- Hide quoted text -
>>
>> > - Show quoted text -
>>
>> It won't work for me because even if I format them as 'text'
>> 1. '0' disappears
>> 2. I need to double click the cell in order to make the cell "Number
>> stored as Text"- Hide quoted text -
>>
>> - Show quoted text -
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

Bonsour® James8309 avec ferveur ;o))) vous nous disiez :

> In Column A:A I have phone numbers in each cell and they are in this
> format [Special -> Dutch -> Telefoonnummer]
> e.g. 0290099009
> Q: How do I change those numbers into 'Number stored in Text'
> Since I have like 40,000 numbers I don't think I can do it by
> manaully.

For Each cell In Selection
cell.Value = cell.Text
Next

HTH
--
--
@+
;o)))

On Jul 7, 9:49*pm, Pete_UK <pashu...@auditel.net> wrote:
> If the phone numbers have already been converted into real numbers,
> then in a helper column you can use this formula:
>
> ="0"&A1
>
> and then copy down. If you fix the values (<copy> the helper cells,
> then Edit | Paste Special | Values | OK then <Esc>), you can then copy
> the helper column to over-write the original values in column A, and
> then delete the helper column.
>
> Hope this helps.
>
> Pete
>
> On Jul 7, 7:46*am, James8309 <jaedong1...@gmail.com> wrote:
>
>
>
> > On Jul 7, 4:40*pm, Dave Mills <Ne...@nospam--djmills-dot-co.uk> wrote:
>
> > > What if you just format them as "text"
>
> > > On Sun, 6 Jul 2008 23:16:36 -0700 (PDT), James8309 <jaedong1...@gmail..com>
> > > wrote:
>
> > > >Hi Everyone,
>
> > > >In Column A:A I have phone numbers in each cell and they are in this
> > > >format [Special -> Dutch -> Telefoonnummer]
> > > >e.g. 0290099009
>
> > > >If I make it into General '0' infront will disappear.
> > > >i.e. 290099009
>
> > > >Q: How do I change those numbers into 'Number stored in Text' (Those
> > > >numbers where little green box thingy appear on the top left of the
> > > >cell)? If I just change the format, it becomes 290099009 again and I
> > > >have to type '0' manually then it becomes 'Number stored as Text'.
> > > >Since I have like 40,000 numbers I don't think I can do it by
> > > >manaully.
>
> > > >thank you for your help.
>
> > > --
> > > Dave Mills
> > > There are 10 type of people, those that understand binary and those that don't.- Hide quoted text -
>
> > > - Show quoted text -
>
> > It won't work for me because even if I format them as 'text'
> > 1. '0' disappears
> > 2. I need to double click the cell in order to make the cell "Number
> > stored as Text"- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Thank you everyone

Regards,

James