Number format

[James8309]

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]

What if you just format them as "text"

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.

 

[James8309]

What if you just format them as "text"

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"

 

[pub]

What if you just format them as "text"

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.

 

[Harald Battran]

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

 

[Pete_UK]

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

What if you just format them as "text"

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.

- 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]

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

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

What if you just format them as "text"

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.

- 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 -

 

[Mais qui est Paul]

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

 

[James8309]

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

What if you just format them as "text"
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.

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