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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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,"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.