View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Joe User[_2_] Joe User[_2_] is offline
external usenet poster
 
Posts: 905
Default How to arrange number?

"Sherees" wrote:
could u pls tell me why it shows a number like
9.66501E+11(this)


Because the cell is formatted as General, and it is not wide enough for the
result.

Simply widen the cell. Select the entire column, then click on Format
Column Autofit (in Excel 2003).

I would format it as Number. Then, if the cell is not wide enough, you will
see "####", which is the normal signal that tells you that the cell is not
wide enough.

But I would not convert text (the original phone numbers) to numbers in the
first place. That will change any phone number that can be more than 15
characters without leading zeros and interstitial blanks. (Is that possible
with some international phone numbers?)

Returning to your original question, I would do:

=SUBSTITUTE(SUBSTITUTE(A1," ",""),"0","")

You may still need to widen the column.


----- original message -----

"Sherees" wrote:
Hi
Thank u for ur reply, when i pasted ur formula its showing a number like
this 9.66501E+11
i want the number without decimal value coz these are mobile numbers with
country code and could u pls tell me why it shows a number like
9.66501E+11(this)
--
Life isa journey not a destination


"Ms-Exl-Learner" wrote:

Assume that you are having the value in A1 cell like the below:-

A1 cell
0056 4 65

Paste the below formula in B1 cell
=VALUE(SUBSTITUTE(A1," ",""))

Change the cell reference to your desired cell, if required.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Sherees" wrote:

Hi
I have a column of around 2000 customer mobile numbers, the numbers are
having space in between nad some numbers entered with two zero in the front.
How can i arrange all the numbers without zero in front and without any space
in between? please guide me with the easiest method

Thank u
--
Life isa journey not a destination