How to replace blank cells with a dash

[wardnine]

Hey, I was wondering how you would go about replacing all blank cells in
a certain range with a dash?? I hope this wouldn't require Visual Basic. Was also wondering how you can change a cell with a given number to a given text (i.e. all cells with the number '1' converted to 'YES', all cells with the number '2' converted to 'NO', etc...) Thanks for any advice!!

 

[Smitty]

Welcome to the Board!

For the first part, highlight the range of cells that you want this format applied to, then goto Format-->Cells-->Number-->Custom and put this in the Type box: _(* #,##0.0000_);_(* (#,##0.0000);_(* "-"????_);_(@_)

For the second part a Find & Replace (CTRL+H) should work.

I.E. Find What-->1; Replace With-->Yes, etc.

Hope that helps,

Smitty

 

[wardnine]

I tried that and it replaced all the zeroes I had with dashes? I wanted to replace the blank cells, not zeroes with dashes, unless there was something I did wrong?? Thanks!

 

[Legacy 21301]

Needs to be done with VBA.

 

[Aladin Akyurek]

Needs to be done with VBA.

Agree.

I wonder however how much of the spreadsheet errors are due to such interventions. Since there is no history, the effects are uncontrlolable, that's, no audit possible.

 

[Jak]

Hi wardnine

To replace the blank cells in your range with a dash without losing your zero's try this:

1. Highlight your range
2. Click on Edit, GoTo
3. Click on Special...
4. Click on Blanks. This will highlight all the blank cells in your selected range.
5. Click Ctrl&H to bring up the Find and Replace dialog box
6. Under Find What, leave this blank. Under Replace with add a dash and click Replace All.

All your balnk cells should now have a Dash.

HTH

 

[Smitty]

Aladin's comments should be noted, but as requested, this will put a hyphen in any blank cell in your used range.

Sub BlankMe()
    For Each c In ActiveSheet.UsedRange
        If c.Value = "" Then c.Value = "-"
        c.HorizontalAlignment = xlCenter
    Next c
End Sub

Hope that helps,

Smitty