How to replace blank cells with a dash

wardnine

Board Regular
Joined
Feb 27, 2004
Messages
56
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!!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Feb 27, 2004
Messages
56
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!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Ponsy Nob. said:
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

Well-known Member
Joined
Apr 5, 2002
Messages
825
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

Legend
Joined
May 15, 2003
Messages
29,536
Aladin's comments should be noted, but as requested, this will put a hyphen in any blank cell in your used range.

Code:
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top