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>

 

[Smitty]

You can pre-format the column as Special-->Phone Number or Text.

Once you've done that you'll need to edit the existing cells and remove the =, but new entries should be fine.

HTH,

 

[jtakw]

Hi,

Take the = sign out.

Excel 2010
	D	E
2	-9826	With = sign
3	1-216-378-9233	Without = sign
Sheet1

Cell Formulas
Range	Formula
D2	=1-216-378-9233

 

[Abbas999]

You can pre-format the column as Special-->Phone Number or Text.

Once you've done that you'll need to edit the existing cells and remove the =, but new entries should be fine.

HTH,

This is working thanks. Do you know about converting $500.00K to $.5M and all other values in excel?

 

[Abbas999]

Hi,

Take the = sign out.

Excel 2010

	D	E
2	-9826	With = sign
3	1-216-378-9233	Without = sign

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

Sheet1​

Worksheet Formulas Cell Formula D2 =1-216-378-9233 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Did't get it because newbie in using formulas. But it looks good. Problem is about 90% solved. Anyway thank you very much for giving your time to this thread.

 

[jtakw]

Did't get it because newbie in using formulas. But it looks good. Problem is about 90% solved. Anyway thank you very much for giving your time to this thread.

Do you mean you didn't get what I meant?

In my sample D2 above, if you put =1-216-378-9233 in the cell, with the = sign, that becomes a formula, not text, so you're telling Excel to calculate 1 minus 216 minus 378 minus 9233, the result is -9826, so take out the = sign and you'll be fine.

For your question regarding the Formatting:

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

 

[Abbas999]

Do you mean you didn't get what I meant?

In my sample D2 above, if you put =1-216-378-9233 in the cell, with the = sign, that becomes a formula, not text, so you're telling Excel to calculate 1 minus 216 minus 378 minus 9233, the result is -9826, so take out the = sign and you'll be fine.

For your question regarding the Formatting:

Excel 2010

	B	C	D	E
1				After Custom Format, enter as
2	$500.00K	$.5 M		500000
3	$180.00K	$.18 M		180000
4	$910.00K	$.91 M		910000
5	$2.8M	$2.8 M		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></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

you misunderstood me. I said I am not good at using formulas. But the solution you psoted looks very good. For Phone number problem i did this:

I chose custom format as Text and when i have 300 rows of number with "=" sign. I did CTRL + H and I chose to find = sign replace it with nothing and all the numbers showed in normal.

For $k to $m. This is working as you posted the solution but the problem is i have 300 rows with mix 223K and $.29M values. Is there a quick way to convert them in bulk? or i have to manually type 200K to 200,000 in order to get 0.2M Please let me know if there is a way. Thank you very for helping

 

[jtakw]

Sorry, no easy way to convert the cells that already has values....

 

[pvr928]

Abbas999

Regarding your translation of numbers issue, if I understand correctly, you have approximately 300 rows with a mix of numbers such as 223,000, 0.29, 450,000, 0.96 etc, and you want all of them to be whole numbers so when you apply the Custom Format { $#,##.0,," M" } they all end up looking like $0.2 M, $0.3 M, $0.5 M, $1.0 M, etc.

Therefore, perhaps apply the following formula to the numbers (assuming the numbers are in Column A and start in A1), place in B1:

If(A1<0,A1*1000000,A1)

and then copy down.

Then if you wish, copy - paste special - values the results in Column B into Column A and then format Column A using the Custom Format.

Just a note of caution using the Custom Format - it can get confusing when this is used as people can tend to think the actual number is a decimal, rather than a whole number formatted in that way. If you then use the number as displayed, if you don't realize it is actually a whole number, the result of any formula can be incorrect if you don't take the formatting into account. I would rather the actual number be what is displayed - then there can be no confusion as to what the actual number is.

Have fun !

Cheers

pvr928

 

[jtakw]

Abbas999

Regarding your translation of numbers issue, if I understand correctly, you have approximately 300 rows with a mix of numbers such as 223,000, 0.29, 450,000, 0.96 etc, and you want all of them to be whole numbers so when you apply the Custom Format { $#,##.0,," M" } they all end up looking like $0.2 M, $0.3 M, $0.5 M, $1.0 M, etc.

Therefore, perhaps apply the following formula to the numbers (assuming the numbers are in Column A and start in A1), place in B1:

If(A1<0,A1*1000000,A1)

and then copy down.

Then if you wish, copy - paste special - values the results in Column B into Column A and then format Column A using the Custom Format.

Just a note of caution using the Custom Format - it can get confusing when this is used as people can tend to think the actual number is a decimal, rather than a whole number formatted in that way. If you then use the number as displayed, if you don't realize it is actually a whole number, the result of any formula can be incorrect if you don't take the formatting into account. I would rather the actual number be what is displayed - then there can be no confusion as to what the actual number is.

Have fun !

Cheers

pvr928

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,""))