I am trying to remove the $- from the cells with no data on a large spread sheet

gch777 · #1 07-04-2018, 02:06 PM

$99.88 $- $- $- $- $- $- $- $- $- $0.53 $- $- $13.90 $- $6.00 $6.96 $- $0.12 $- $0.03 $- $127.42

The sheet needs to be just blank in the cells where the $- currently are
any help would be great

ArviLaanemets · #2 07-04-2018, 09:49 PM

The easiest way - use numeric format instead of money format!

In case all money info is in dollars, there is no need to display it - proper column header will indicate, that the numbers in column are for money;

In case you have different currencies in table, the formatting doesn't work anyway. You must have the cyrrency in column header (SUM_USD, SUM_EUR, ...) or in separate column.

Logit · #3 07-05-2018, 09:15 AM

Code:

Sub Test()
    Dim rng As Range
    Dim cel As Range
        
    Set rng = Range("A1:Z100")  '<-- change range as required.
    Application.ScreenUpdating = False
        For Each cel In rng
            If cel.Value = "$-" Then cel.ClearContents
        Next cel
    Application.ScreenUpdating = True
End Sub

Pecoflyer · #4 07-05-2018, 09:42 AM

Or
Ctrl+H
Replace what ~$- (that is tilde followed by dollar sign followed by dash)
Replace with ( leve blank)

p { margin-bottom: 0.25cm; line-height: 120%; }

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Data from a specific row in a spread sheet into a form	JohnnySTI	Excel Programming	13	01-24-2018 01:20 PM
Simple Spread Sheet	TThomas	Excel	1	04-07-2016 09:01 PM
Using combobox to pass data from an Excel spread sheet to text boxes on userform	Stewie840	Word VBA	14	12-17-2015 10:13 PM
Copy data from certain Cells in new sheet	zain_shaikh	Excel Programming	1	02-11-2015 07:35 PM
Condensing a spread sheet	hawkeyefxr	Excel	4	08-22-2012 05:17 AM