convert alpha to a numeric value

  • Thread starter Thread starter geode
  • Start date Start date
G

geode

I received data from a user where negative numbers are
sent as : "31Neg" for a "-31".
Can you suggest a way to create a real negative number.
Thanks in advance for you help.
 
You could use a macro

Sub ConvAlpha()

For Each c In ActiveSheet.UsedRange
v = c.Value
If Right(c, 3) = "Neg" Then
c.Value = Left(v, Len(v) - 3) * -1
End If
Next c

End Sub

Not very efficient though, so if the sheet is huge it will take time.
 
Geode, use a formula "left(A1,2)*-1. This assumes, the numbers start in A1,
they are always 2 digits. If not use "left(A1,Find("N",A1,1)-1)*-1". Copy
range and paste special values. HTH

Greg
 
Here's one way.
Insert the below code in a general module
(<Alt><F11>, Insert > Module)

Sub MakeNegativeNumber()
'Leo Heuser, 2 Jan. 2004
Dim Cell As Range

For Each Cell In ActiveSheet.UsedRange.Cells
If UCase(Right(Cell.Value, 3)) = "NEG" Then
With Cell
.Replace what:="Neg", replacement:="", _
MatchCase:=False
.NumberFormat = "General"
.Value = -.Value
End With
End If
Next Cell

End Sub

From the sheet in question run the macro from
Tools > Macro > Macros

Watch out, that no cell apart from the negative cells
end with the three letters NEG (or Neg or neg)!!
 
Try

=SUBSTITUTE(A1,"NEG","")*-1

in A2 where A1 contains "31NEG". It appears to be case sensitive so watch
the caps.
 
Try this formula

=VALUE(LEFT(A2,FIND("NEG",A2)-1))*-1


where A2 is the cell with "31NEG"
 
Or even:

=SUBSTITUTE(LOWER(A1),"neg","")*-1

to avoid the upper/lower problem.

But only use this kind of formula on the cells that have "Neg" in them. It'll
damage the positive ones.
 
Back
Top