text within cell seen as ####

[David Renick]

I have a column that is formatted as text; a few cells will show severa
number signs in place of the text that is in the cell. Changing th
cells width and row height has no effect. There is no apparen
difference in the text between cells that display normal and cells tha
display ### signs.

Changing the format to general will cure the cell, but I'm trying no
of have search the workbook and change every cell that doesn't displa
correctly.

Any suggestions

:confused

 

[Frank Kabel]

Hi
if you re-format the cells what values do you see (negative values?)

 

[David Renick]

Frank,
The text field is reference disignators(electronics term):


Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11,Q12,Q13,Q14,Q15,Q16,Q17,Q18


Dav

 

[Dave Peterson]

Maybe you could use a macro:

Option Explicit
Sub testme01()

Dim FoundCell As Range

With ActiveSheet.UsedRange
Set FoundCell = .Cells.Find(What:="######", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not FoundCell Is Nothing Then
Do
With FoundCell
If .NumberFormat = "@" Then
.NumberFormat = "General"
Else
'do nothing
End If
End With

Set FoundCell = .FindNext(FoundCell)

If FoundCell Is Nothing Then
Exit Do
End If

Loop
End If
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm