Empty Cells

  • Thread starter Thread starter dk
  • Start date Start date
D

dk

Can you give us a clear idea for emptying non numeric charachters from cells
& keeping the numeric part of the cell and also for emptying the whole cell
 
To empty cells, select them and touch the Delete key.

To remove non-numeric characters from cells, select them and run this macro:

Sub numerifyy()
For Each r In Selection
v = r.Value
L = Len(v)
s = ""
For i = 1 To L
ch = Mid(v, i, 1)
If ch Like "*[-0-9]*" Or ch = "." Then
s = s & ch
End If
Next
If Len(s) > 0 Then
r.Value = --s
End If
Next
End Sub
 
Thanks for reply
but we get a error debug 13
Also can we select specifc cells to run macro

Gary''s Student said:
To empty cells, select them and touch the Delete key.

To remove non-numeric characters from cells, select them and run this macro:

Sub numerifyy()
For Each r In Selection
v = r.Value
L = Len(v)
s = ""
For i = 1 To L
ch = Mid(v, i, 1)
If ch Like "*[-0-9]*" Or ch = "." Then
s = s & ch
End If
Next
If Len(s) > 0 Then
r.Value = --s
End If
Next
End Sub



--
Gary''s Student - gsnu200805


dk said:
Can you give us a clear idea for emptying non numeric charachters from cells
& keeping the numeric part of the cell and also for emptying the whole cell
Click to expand...
Click to expand...
 
Which cells?
--
Gary''s Student - gsnu200805


dk said:
Thanks for reply
but we get a error debug 13
Also can we select specifc cells to run macro

Gary''s Student said:
To empty cells, select them and touch the Delete key.

To remove non-numeric characters from cells, select them and run this macro:

Sub numerifyy()
For Each r In Selection
v = r.Value
L = Len(v)
s = ""
For i = 1 To L
ch = Mid(v, i, 1)
If ch Like "*[-0-9]*" Or ch = "." Then
s = s & ch
End If
Next
If Len(s) > 0 Then
r.Value = --s
End If
Next
End Sub



--
Gary''s Student - gsnu200805


dk said:
Can you give us a clear idea for emptying non numeric charachters from cells
& keeping the numeric part of the cell and also for emptying the whole cell
Click to expand...
Click to expand...
Click to expand...
 
the cell we want to run macro
also meantime it's crashing debug

Gary''s Student said:
Which cells?
--
Gary''s Student - gsnu200805


dk said:
Thanks for reply
but we get a error debug 13
Also can we select specifc cells to run macro

Gary''s Student said:
To empty cells, select them and touch the Delete key.

To remove non-numeric characters from cells, select them and run this macro:

Sub numerifyy()
For Each r In Selection
v = r.Value
L = Len(v)
s = ""
For i = 1 To L
ch = Mid(v, i, 1)
If ch Like "*[-0-9]*" Or ch = "." Then
s = s & ch
End If
Next
If Len(s) > 0 Then
r.Value = --s
End If
Next
End Sub



--
Gary''s Student - gsnu200805


:

Can you give us a clear idea for emptying non numeric charachters from cells
& keeping the numeric part of the cell and also for emptying the whole cell
Click to expand...
Click to expand...
Click to expand...
 
Does this mean none of the code I gave you two days ago is of any use to
you?

Or does it mean you did not try it?


Gord Dibben MS Excel MVP
 
I also think that if you can provide some sample data that it will really
help those trying to help you.

There is a difference in trying to come up with a process to separate an
entry such as "abc123" and entries like
'abc-123' (is the - a dash or a negative symbol?)
or
"Tic-Tac-Toe is played on a 3x3 grid"
what's numeric and what's not in that? And if we consider x to be numeric,
that creates headaches on down the road.

Is there the possibility that a cell will be empty (as in nothing there or
displayed)?

You have 2 of the best trying to assist you now, help them to help you and
you'll get something worth having in the end.
 
no didn"t work
ex:(9-

44
^

46

47

50
51

52

921
54

56

66

-69

(4/

70



%
0

$/

83

94

44^

87 .


%


91

0
4%

94

41
95

96 ?
%

$4
 
Works for me with your example data posted.

It won't remove the % from 4% or $4 because that is formatting.

Format the cells to general and the % and $ will go away.


Gord
 
Were basically scaning sheets of no. we then import to excel sometimes the
pages have slighmakrs the ocr gives all types of characters even tough it's
set only for numeric so we need to modify different types of ways sometimes
the whole range of cells is false & only the . -#$ etc is false that's what
we trying to do
this for daily use pages & pages
 
Back
Top