Forumla HELP with character & number

[HotNumbers]

I have a excel download from a database. When looking at a cell (example D10) the only thing visible is the following character *; however, when i click into the cell i can see a number for example 1,124. Can someone please help me write a formula in column E that will look in to column D and writing a formula that identifies that it is a non numeric cell. I have tried a IF statement it will not work since when you click on a cell a number appears. The * is only visible when clicking away from the cell.

 

[Robert Mika]

Have you tried to change the format of the cell?
Wht do you see in Formula Bar?
Could you post an screenshot of your sheet?

 

[HotNumbers]

How can I post the image?

 

[HotNumbers]

AS you can see below data on column J appears with a *, But as you can see when you click on a cell it shows a number. see below

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1686</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">TH</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">$0.00</td></tr><tr ><td style="color: #161120;text-align: center;">1687</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-4,097.71 ARS</td></tr><tr ><td style="color: #161120;text-align: center;">1688</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">GB</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM56</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-£ 480.00</td></tr><tr ><td style="color: #161120;text-align: center;">1689</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-$ 109.51</td></tr><tr ><td style="color: #161120;text-align: center;">1690</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">BR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-*</td></tr><tr ><td style="color: #161120;text-align: center;">1691</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">CN</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-$ 1,782.00</td></tr><tr ><td style="color: #161120;text-align: center;">1692</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">CZ</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-$ 964.44</td></tr><tr ><td style="color: #161120;text-align: center;">1693</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">DE</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-*</td></tr><tr ><td style="color: #161120;text-align: center;">1694</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">ES</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-*</td></tr><tr ><td style="color: #161120;text-align: center;">1695</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">FR</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #E0E5E8;;">AM57</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">-*</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Currency</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1687</th><td style="text-align:left">AR</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1687</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1687</th><td style="text-align:left">-4097.71</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1688</th><td style="text-align:left">GB</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1688</th><td style="text-align:left">AM56</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1688</th><td style="text-align:left">-480</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1689</th><td style="text-align:left">AR</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1689</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1689</th><td style="text-align:left">-109.51</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1690</th><td style="text-align:left">BR</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1690</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1690</th><td style="text-align:left">-35758.62</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1691</th><td style="text-align:left">CN</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1691</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1691</th><td style="text-align:left">-1782</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1692</th><td style="text-align:left">CZ</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1692</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1692</th><td style="text-align:left">-964.44</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1693</th><td style="text-align:left">DE</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1693</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1693</th><td style="text-align:left">-24636.52</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1694</th><td style="text-align:left">ES</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1694</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1694</th><td style="text-align:left">-5952.03</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1695</th><td style="text-align:left">FR</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I1695</th><td style="text-align:left">AM57</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J1695</th><td style="text-align:left">-39463.88</td></tr></tbody></table></td></tr></table><br />

 

[Peter_SSs]

I suspect that your '*' cells are not text but numerical with a Custom Format to show the '*' under some condition.

If you select one of those '*' cells then on the Home ribbon tab, look at the 'Number' group and see what is showing in the box at the top of that ribbon group. Is it 'Custom'?

If so, with that cell still selected, click the little arrow at the bottom right of the 'Number' ribbon group and the Format Cells dialog should pop up showing you what the Custom Format actually is.

What do you really want to do with those cells?