View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Ms-Exl-Learner Ms-Exl-Learner is offline
external usenet poster
 
Posts: 506
Default How to arrange number?

If the Number is within 15 digits then format the cell like the below:

Select the range and do Right Click and select Format
CellsNumberCategoryCustom paste the below format

###########0000

Or

###############

If the number is exceeding 15 digits then you need to use the formula like
below:

="'"&VALUE(SUBSTITUTE(A1," ",""))

But the above formula will result you text character instead of Original
Numbers.

Remember to Click Yes, if this post helps!

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


"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