Excel 07 - text displays as pound signs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mdavison - 14 May 2008 18:11 GMT

OK, I'm not new to Excel, just 07. And this is killing me. I have a user who
uses Excel to track discussions of phone interviews, so she's using one cell
in which to type her comments. MOST of the time, the text is fine. Randomly,
any given cell will suddenly appear as all pound signs. Thes eare the things
I have tried and failed to prove consistent fixes:

1 - number of characters is irrelevant, happens on long and short text
2 - I make sure there is no hard return at end of text
3 - copy /past into plain text and bring back (sometimes works, sometimes not)
4 - changed font across entire tab sheet. nada
5 - remove empty space AND period at end of her typing. It worked once then
on today's two did nothing for me.

I have only seen this happen when text is exported from a SQL dbase and left
funky symbols in with the characters. But that was also importing into 03, so
this 07 newly typed in freak has me stumped.

Reply to this Message

Dave Peterson - 14 May 2008 18:53 GMT

(Saved from a previous post)

It could mean a few things.

1. The columnwidth is too narrow to show the number.

Widen the column or change the font size of that cell. Or change the
numberformat to General.

2. You have a date/time in that cell and it's negative

Don't use negative dates. If excel was helping you, it may have
changed the format to a date. Change it back to General (or some
other number format).

If you need to see negative date/times:
Tools|options|Calculation Tab|and check 1904 date system
(but this can cause trouble--watch what happens to your dates
and watch what happens when you copy|paste dates to a different
workbook that doesn't use this setting)

3. You have a lot of text in the cell, the cell is formatted as Text.

Format the cell as general.

4. You really have ###'s in that cell.

Clean up that cell.

5. You have # in a cell, but it's format is set to Fill.

Change the format
(format|cells|alignment tab|horizontal box, change it to General.

> OK, I'm not new to Excel, just 07. And this is killing me. I have a user who
> uses Excel to track discussions of phone interviews, so she's using one cell
[quoted text clipped - 12 lines]
> funky symbols in with the characters. But that was also importing into 03, so
> this 07 newly typed in freak has me stumped.

Signature

Dave Peterson

Reply to this Message

mdavison - 14 May 2008 21:10 GMT

I'm sorry, I thought I made it pretty clear that this is all English language
text - alpha characters. I also think I mentioned (I'm sorry if I did not)
that I cleared ALL formatting from the entire sheet - so 'General' would be
the default cell setting.

Anyone else experience this? And again - its RANDOM. Multiple cells in other
locations in the same column have no problem withtis. Sometimes Format
painter works, sometimes it doesn't.

> (Saved from a previous post)
>
[quoted text clipped - 46 lines]
> > funky symbols in with the characters. But that was also importing into 03, so
> > this 07 newly typed in freak has me stumped.

Reply to this Message

Dave Peterson - 14 May 2008 22:58 GMT

Did you actually clear the formatting after the contents were in the cell?

Just curious.

> I'm sorry, I thought I made it pretty clear that this is all English language
> text - alpha characters. I also think I mentioned (I'm sorry if I did not)
[quoted text clipped - 59 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

mdavison - 15 May 2008 13:46 GMT

Yes. It did nothing.

> Did you actually clear the formatting after the contents were in the cell?
>
[quoted text clipped - 63 lines]
> > >
> > > Dave Peterson

Reply to this Message

A Gelmers - 16 May 2008 14:55 GMT

Dave, I hope you have time to address a question I have.

You wrote the following macro to automatically paste formats every time a new cell was selected:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect Password:="hi"

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Worksheets("formatbackup").Cells.Copy
Me.Range("a1").PasteSpecial Paste:=xlPasteFormats
Target.Select

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Me.Protect Password:="hi"

End Sub

This works great, but prevents copying and pasting of similar info on the sheet to other cells on the sheet. I am also interested in adding a step to apply conditional formatting.

In a thread I found on line you indicated that application of formatting could be done right before the workbook is saved. Could you show me an edited macro to paste formats, paste conditional formats and apply right before save?

Thank you. And I would like to say how impressed and appreciative I am that you seem to be an origin source of assistance on the internet for these type of Excel issues.

Thank you,
Andy Gelmers
(918) 342-7280

Reply to this Message

Dave Peterson - 16 May 2008 15:25 GMT

Running macros can destroy the undo/redo stack and can kill the clipboard.
Sometimes that's ok. Sometimes not.

This goes behind the ThisWorkbook module--not behind a worksheet and not in a
General module:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim FBwks As Worksheet
Dim wks As Worksheet
Dim CurCell As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set CurCell = ActiveCell

Set FBwks = Me.Worksheets("formatbackup")
Set wks = Me.Worksheets("sheet999")

wks.Unprotect Password:="hi"

FBwks.Cells.Copy
wks.Range("a1").PasteSpecial Paste:=xlPasteFormats
wks.Protect Password:="hi"

Application.Goto CurCell

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

You could also do the same thing right when the workbook is opened.

Just change:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
to
Private Sub Workbook_Open()

Depends on when you want to do it...

A, Gelmers wrote:

> Dave, I hope you have time to address a question I have.
>
[quoted text clipped - 32 lines]
> Andy Gelmers
> (918) 342-7280

Signature

Dave Peterson

Reply to this Message