removing symbols from numbers

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hello,
I have a problem with removing objects from numbers. The
first problem is that I would like to remove a # symbol
and just get the real value.
For example I have values of #4 and #15 and would like it
to read just 4 and 15.

The other problem is that I have numbers such as 185/10
and 45/0 and so forth. I would only want the whole value
185 and 45 and not the symbol / or anything after it.
Can you help me.

Thank you

Chris
 
Hi Chris

In the first case if there is always a single # in fron of the number
use
=MID(A1,2,10)*1
(this assumes that the numeric part of your answer will never be more than
10 digits, if so alter the number accordingly)

For the second case use
=LEFT(A1,FIND("/",A1)-1)*1
 
Hi Chris,

If you want to just delete a single character from a range
of cells, you can just select the cells you want to
perform the delete in; hit ctrl-H for find and replace;
key in the # character; leave the second field blank; and
hit replace all.

This code should do your second task. When you run the
macro any cells that are highlighted will have the slash
and the data following it trimmed.

Sub TrimSlash()
Dim intSlash As Integer

Dim rngRow As Range
For Each rngRow In Selection
intSlash = InStr(1, rngRow.Value, "/",
vbTextCompare)
If intSlash <> 0 Then
rngRow.Value = Left(rngRow.Value, intSlash - 1)
End If
Next
End Sub


HTH.

-Brad
 
Back
Top