A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to arrange number?



 
 
Thread Tools Display Modes
  #1  
Old January 31st 10, 01:05 PM posted to microsoft.public.excel.misc
Sherees
external usenet poster
 
Posts: 43
Default How to arrange number?

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
Ads
  #2  
Old January 31st 10, 01:10 PM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default How to arrange number?

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

  #3  
Old January 31st 10, 01:18 PM posted to microsoft.public.excel.misc
Sherees
external usenet poster
 
Posts: 43
Default How to arrange number?

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

  #4  
Old January 31st 10, 01:57 PM posted to microsoft.public.excel.misc
Ms-Exl-Learner
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
Cells>>Number>>Category>>Custom>> 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

  #5  
Old January 31st 10, 05:39 PM posted to microsoft.public.excel.misc
Joe User[_2_]
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

  #6  
Old January 31st 10, 05:47 PM posted to microsoft.public.excel.misc
Joe User[_2_]
external usenet poster
 
Posts: 905
Default How to arrange number?

"Ms-Exl-Learner" wrote:
> Select the range and do Right Click and select Format
> Cells>>Number>>Category>>Custom>> 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
> Cells>>Number>>Category>>Custom>> 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

  #7  
Old February 1st 10, 07:00 AM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default How to arrange number?

Hi Joe,

I don’t think so widening of columns will work if the number Exceeds 12
digits when it is formatted as General. If cell is in General format and
widening of column will also result the same 1.23457E+13 error.

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.

Yes, I though that using apostrophe in front of value function will mark the
cell as text entry and it will show the numbers exactly even though the
length of the number goes beyond 16 digits. But now I realize that this is
the wrong assumption.

But I am seeing the same error in your below formula also,

="'"&VALUE("1234567890123456")

Have you applied the above formula in excel? Because the Length of the
Number is exceeding 15 digits, so it will get the wrong result even though
the cell is formatted as numbers. You can notice that the end value Six will
be changed into zero after pasting the above formula in a cell.

I think there is no benefit of using the apostrophe in front of the value
function, because Value is working with the same methodology like Number
format. Since both Number format and Value functions are showing the correct
results when the length of the Number is upto 15 digits.

Finally about the below formula, substituting of 0 (zero) will remove all
the zeros and result you the text numbers without any zeros.

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

But the OP is looking to remove the leading zeros like 00020680089005089046
to 20680089005089046. But the above formula will result 2688958946.

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


"Joe User" wrote:

> "Ms-Exl-Learner" wrote:
> > Select the range and do Right Click and select Format
> > Cells>>Number>>Category>>Custom>> 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
> > Cells>>Number>>Category>>Custom>> 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

  #8  
Old February 1st 10, 08:35 AM posted to microsoft.public.excel.misc
Joe User[_2_]
external usenet poster
 
Posts: 905
Default How to arrange number?

"Ms-Exl-Learner" > wrote:
> I don’t think so widening of columns will work if the number Exceeds 12
> digits when it is formatted as General.


Correct. Well, it does not work if the number exceeds 10 digits.

In my more-complete response to the OP, I suggested combining that with
formatting as Number. Arguably, your suggestion avoids the extra step; the
column is widened automagically.


> But I am seeing the same error in your below formula also,
> ="'"&VALUE("1234567890123456")

[....]
> You can notice that the end value Six will be changed into
> zero after pasting the above formula in a cell.


That was the point. It is a counter-example to demonstrate that the
apostrophe does not have the desired effect when used in that way. If the
apostrophe had worked as you intended, the last 6 would not be changed to
zero.


> Finally about the below formula, substituting of 0 (zero) will remove all
> the zeros and result you the text numbers without any zeros.
> =SUBSTITUTE(SUBSTITUTE(A1," ",""),"0","")
> But the OP is looking to remove the leading zeros


You are correct. My bad.

But my point was: If the phone number might be more than 15 digits long
after removing leading zeros, then it is ill-advised to try to remove
leading zeros by converting the text to a number.


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

"Ms-Exl-Learner" > wrote in message
...
> Hi Joe,
>
> I don’t think so widening of columns will work if the number Exceeds 12
> digits when it is formatted as General. If cell is in General format and
> widening of column will also result the same 1.23457E+13 error.
>
> 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.
>
> Yes, I though that using apostrophe in front of value function will mark
> the
> cell as text entry and it will show the numbers exactly even though the
> length of the number goes beyond 16 digits. But now I realize that this
> is
> the wrong assumption.
>
> But I am seeing the same error in your below formula also,
>
> ="'"&VALUE("1234567890123456")
>
> Have you applied the above formula in excel? Because the Length of the
> Number is exceeding 15 digits, so it will get the wrong result even though
> the cell is formatted as numbers. You can notice that the end value Six
> will
> be changed into zero after pasting the above formula in a cell.
>
> I think there is no benefit of using the apostrophe in front of the value
> function, because Value is working with the same methodology like Number
> format. Since both Number format and Value functions are showing the
> correct
> results when the length of the Number is upto 15 digits.
>
> Finally about the below formula, substituting of 0 (zero) will remove all
> the zeros and result you the text numbers without any zeros.
>
> =SUBSTITUTE(SUBSTITUTE(A1," ",""),"0","")
>
> But the OP is looking to remove the leading zeros like
> 00020680089005089046
> to 20680089005089046. But the above formula will result 2688958946.
>
> --------------------
> (Ms-Exl-Learner)
> --------------------
>
>
> "Joe User" wrote:
>
>> "Ms-Exl-Learner" wrote:
>> > Select the range and do Right Click and select Format
>> > Cells>>Number>>Category>>Custom>> 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
>> > Cells>>Number>>Category>>Custom>> 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


  #9  
Old February 1st 10, 08:48 AM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default How to arrange number?

I think we can use the Value function for the partial data for removing the
leading zeros like the below:-

=IF(A1="","","'"&SUBSTITUTE(VALUE(LEFT(SUBSTITUTE( A1,"
",""),10))&MID(SUBSTITUTE(A1," ",""),11,255)," ",""))

OR

=IF(A1="","","'"&VALUE(LEFT(SUBSTITUTE(A1," ",""),10))&MID(SUBSTITUTE(A1,"
",""),11,255))

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


"Joe User" wrote:

> "Ms-Exl-Learner" wrote:
> > Select the range and do Right Click and select Format
> > Cells>>Number>>Category>>Custom>> 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
> > Cells>>Number>>Category>>Custom>> 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

  #10  
Old February 1st 10, 01:25 PM posted to microsoft.public.excel.misc
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default How to arrange number?

Hi Joe,

I have gained lot of knowledge by this post and Thanks for suggesting me
some good points.

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


"Joe User" wrote:

> "Ms-Exl-Learner" > wrote:
> > I don’t think so widening of columns will work if the number Exceeds 12
> > digits when it is formatted as General.

>
> Correct. Well, it does not work if the number exceeds 10 digits.
>
> In my more-complete response to the OP, I suggested combining that with
> formatting as Number. Arguably, your suggestion avoids the extra step; the
> column is widened automagically.
>
>
> > But I am seeing the same error in your below formula also,
> > ="'"&VALUE("1234567890123456")

> [....]
> > You can notice that the end value Six will be changed into
> > zero after pasting the above formula in a cell.

>
> That was the point. It is a counter-example to demonstrate that the
> apostrophe does not have the desired effect when used in that way. If the
> apostrophe had worked as you intended, the last 6 would not be changed to
> zero.
>
>
> > Finally about the below formula, substituting of 0 (zero) will remove all
> > the zeros and result you the text numbers without any zeros.
> > =SUBSTITUTE(SUBSTITUTE(A1," ",""),"0","")
> > But the OP is looking to remove the leading zeros

>
> You are correct. My bad.
>
> But my point was: If the phone number might be more than 15 digits long
> after removing leading zeros, then it is ill-advised to try to remove
> leading zeros by converting the text to a number.
>
>
> ----- original message -----
>
> "Ms-Exl-Learner" > wrote in message
> ...
> > Hi Joe,
> >
> > I don’t think so widening of columns will work if the number Exceeds 12
> > digits when it is formatted as General. If cell is in General format and
> > widening of column will also result the same 1.23457E+13 error.
> >
> > 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.
> >
> > Yes, I though that using apostrophe in front of value function will mark
> > the
> > cell as text entry and it will show the numbers exactly even though the
> > length of the number goes beyond 16 digits. But now I realize that this
> > is
> > the wrong assumption.
> >
> > But I am seeing the same error in your below formula also,
> >
> > ="'"&VALUE("1234567890123456")
> >
> > Have you applied the above formula in excel? Because the Length of the
> > Number is exceeding 15 digits, so it will get the wrong result even though
> > the cell is formatted as numbers. You can notice that the end value Six
> > will
> > be changed into zero after pasting the above formula in a cell.
> >
> > I think there is no benefit of using the apostrophe in front of the value
> > function, because Value is working with the same methodology like Number
> > format. Since both Number format and Value functions are showing the
> > correct
> > results when the length of the Number is upto 15 digits.
> >
> > Finally about the below formula, substituting of 0 (zero) will remove all
> > the zeros and result you the text numbers without any zeros.
> >
> > =SUBSTITUTE(SUBSTITUTE(A1," ",""),"0","")
> >
> > But the OP is looking to remove the leading zeros like
> > 00020680089005089046
> > to 20680089005089046. But the above formula will result 2688958946.
> >
> > --------------------
> > (Ms-Exl-Learner)
> > --------------------
> >
> >
> > "Joe User" wrote:
> >
> >> "Ms-Exl-Learner" wrote:
> >> > Select the range and do Right Click and select Format
> >> > Cells>>Number>>Category>>Custom>> 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
> >> > Cells>>Number>>Category>>Custom>> 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

>
> .
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
arrange data Suri New Users to Excel 2 November 24th 09 12:11 AM
Auto Arrange Kanmi Excel Worksheet Functions 2 June 23rd 09 09:56 PM
How do I re arrange numbers Raz Excel Worksheet Functions 3 November 12th 08 07:04 PM
Arrange Windows Bean123r Excel Discussion (Misc queries) 0 January 24th 06 09:35 PM
re-arrange text snugs25 Excel Worksheet Functions 1 December 12th 05 01:16 PM


All times are GMT +1. The time now is 09:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.