View Single Post
  #6   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?

"Ms-Exl-Learner" wrote:
Select the range and do Right Click and select Format
CellsNumberCategoryCustom paste the
below format


..... Or simply make the column wide enough.


If the number is exceeding 15 digits then you
need to use the formula like below:
="'"&VALUE(SUBSTITUTE(A1," ",""))


Adding the apostrophe then is too late. The VALUE function will already
have changed the number. Simply try:

="'"&VALUE("1234567890123456")

PS: Appending an apostrophe like that is not the same as typing an
apostrophe manually. In the case above, the apostrophe actually becomes part
of the string, and it is displayed. I'm sure that is not what you intended.


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

"Ms-Exl-Learner" wrote:

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