Phone number format problem in excel

[Abbas999]

Hi Experts,

I am facing a problem. Whenever i put a phone number into cells it converts to formulas and i tried finding the solutions on google and found some solution format cells etc but nothing is helped me. I have shared a pic sample below. Please help if someone knows a solution it. Also I want to convert numbers $500.00K to $0.5M Is that possible?

Thanks

<a href="http://tinypic.com?ref=o7odmv" target="_blank"><img src="http://i64.tinypic.com/o7odmv.png" border="0" alt="Image and video hosting by TinyPic"></a>

 

[pvr928]

Nice one jtakw

cheers

pvr928

 

[Abbas999]

Hi,

That's a good suggestion; however, the OP has entered the values as Text, as indicated by the values being aligned to the left and having K or M in the cell, so in this case, the OP will probably have to either manually change all the cell values OR using your suggestion but with this formula instead:

Excel 2010

	B	C	D	E	F	G
After Custom Format, enter as
$500.00K
$180.00K
$910.00K
$2.8M
Before Custom Format	Using Custom Format $#.##,," M"	Figures will show as in Column C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$.5 M[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$.18 M[/TD]
[TD="align: right"][/TD]
[TD="align: right"]180000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]180000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$.91 M[/TD]
[TD="align: right"][/TD]
[TD="align: right"]910000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]910000[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$2.8 M[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2800000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2800000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(RIGHT(B2,1)="K",MID(B2,2,LEN(B2)-2)*1000,IF(RIGHT(B2,1)="M",MID(B2,2,LEN(B2)-2)*1000000,""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This helped me. Thank you very much all of you.. @jtakw @pvr928 @

 

[Rick Rothstein]

Hi,

That's a good suggestion; however, the OP has entered the values as Text, as indicated by the values being aligned to the left and having K or M in the cell, so in this case, the OP will probably have to either manually change all the cell values OR using your suggestion but with this formula instead:

Excel 2010
	B	C	D	E	F	G
1				After Custom Format, enter as
2	$500.00K	$.5 M		500000		500000
3	$180.00K	$.18 M		180000		180000
4	$910.00K	$.91 M		910000		910000
5	$2.8M	$2.8 M		2800000		2800000
6
7	Before Custom Format	Using Custom Format $#.##,," M"		Figures will show as in Column C
Sheet1

Cell Formulas
Range	Formula
G2	=IF(RIGHT(B2,1)="K",MID(B2,2,LEN(B2)-2)*1000,IF(RIGHT(B2,1)="M",MID(B2,2,LEN(B2)-2)*1000000,""))

Here is another formula that you can use...

=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"M","0,000"),"K","0"),".",",")

 

[jtakw]

This helped me. Thank you very much all of you.. @jtakw @pvr928 @

You're welcome.