Excel 07 - text displays as pound signs

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.

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

mdavison wrote:

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.

--

Dave Peterson

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.




"Dave Peterson" wrote:

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

mdavison wrote:

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.

--

Dave Peterson

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

Just curious.


mdavison wrote:

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.

"Dave Peterson" wrote:

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

mdavison wrote:

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.

--

Dave Peterson


--

Dave Peterson

Yes. It did nothing.

"Dave Peterson" wrote:

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

Just curious.


mdavison wrote:

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.

"Dave Peterson" wrote:

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

mdavison wrote:

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.

--

Dave Peterson


--

Dave Peterson

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

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.

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

--

Dave Peterson

I'm having the same problem with all alpha input and just found this thread. Instead of setting number type to General, I set it to Scientific, and it solved the problem.



mdaviso wrote:

Excel 07 - text displays as pound signs
15-May-08

Yes. It did nothing.

"Dave Peterson" wrote:

Previous Posts In This Thread:

On Wednesday, May 14, 2008 1:11 PM
mdaviso wrote:

Excel 07 - text displays as pound signs
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.

On Wednesday, May 14, 2008 1:53 PM
Dave Peterson wrote:

Excel 07 - text displays as pound signs
(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.

mdavison wrote:

--

Dave Peterson

On Wednesday, May 14, 2008 4:10 PM
mdaviso wrote:

Excel 07 - text displays as pound signs
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.




"Dave Peterson" wrote:

On Wednesday, May 14, 2008 5:58 PM
Dave Peterson wrote:

Excel 07 - text displays as pound signs
Did you actually clear the formatting after the contents were in the cell?

Just curious.


mdavison wrote:

--

Dave Peterson

On Thursday, May 15, 2008 8:46 AM
mdaviso wrote:

Excel 07 - text displays as pound signs
Yes. It did nothing.

"Dave Peterson" wrote:

On Friday, May 16, 2008 9:55 AM
A Gelmers wrote:

Paste Format Macro
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

On Wednesday, June 10, 2009 8:03 AM
Mike Connolly wrote:

Excel 07 - text displays as pound signs - mdaviso
You have exceeded the charagcter limit for a "text" type of cell.
Try changing the formatting on that cell(s) to "General". That should do it.

EggHeadCafe - Software Developer Portal of Choice
WeakReferences, Memory Management, Garbage, and More...
http://www.eggheadcafe.com/tutorials...memory-ma.aspx

I think I'd still use General.

If I typed a number in that cell (replacing the text), I wouldn't want to be
surprised seeing the scientific format.

Elizabeth, Altman wrote:

I'm having the same problem with all alpha input and just found this thread. Instead of setting number type to General, I set it to Scientific, and it solved the problem.

mdaviso wrote:

Excel 07 - text displays as pound signs
15-May-08

Yes. It did nothing.

"Dave Peterson" wrote:

Previous Posts In This Thread:

On Wednesday, May 14, 2008 1:11 PM
mdaviso wrote:

Excel 07 - text displays as pound signs
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.

On Wednesday, May 14, 2008 1:53 PM
Dave Peterson wrote:

Excel 07 - text displays as pound signs
(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.

mdavison wrote:

--

Dave Peterson

On Wednesday, May 14, 2008 4:10 PM
mdaviso wrote:

Excel 07 - text displays as pound signs
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.

"Dave Peterson" wrote:

On Wednesday, May 14, 2008 5:58 PM
Dave Peterson wrote:

Excel 07 - text displays as pound signs
Did you actually clear the formatting after the contents were in the cell?

Just curious.

mdavison wrote:

--

Dave Peterson

On Thursday, May 15, 2008 8:46 AM
mdaviso wrote:

Excel 07 - text displays as pound signs
Yes. It did nothing.

"Dave Peterson" wrote:

On Friday, May 16, 2008 9:55 AM
A Gelmers wrote:

Paste Format Macro
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

On Wednesday, June 10, 2009 8:03 AM
Mike Connolly wrote:

Excel 07 - text displays as pound signs - mdaviso
You have exceeded the charagcter limit for a "text" type of cell.
Try changing the formatting on that cell(s) to "General". That should do it.

EggHeadCafe - Software Developer Portal of Choice
WeakReferences, Memory Management, Garbage, and More...
http://www.eggheadcafe.com/tutorials...memory-ma.aspx

--

Dave Peterson