替换函数和负值 [英] Replace function and negative values

问题描述

因此，我有一堆列需要用字母替换，这是旧的带签名的打孔东西.因此，基本上我想做的是将字母替换为数字，然后对每个单元格乘以0.01.

So, I have a bunch of columns that need to be replaced with a letter, this is old signed overpunch stuff. So basically what I want to do is replace the letters with numbers and then multiply it by 0.01 for each cell.

所以基本上，我将有一列1. 0000012C = 00000123 x 0.01 = 2.462. 0002927B = 29272 x 0.01 = 585.443. 0000245N = -00002455 x 0.01 = -24.55

So basically, I'll have a column like 1. 0000012C = 00000123 x 0.01 = 2.46 2. 0002927B = 29272 x 0.01 = 585.44 3. 0000245N = -00002455 x 0.01 = -24.55

所以我不知道的问题是如何使单元格中的值变为负数.如您所见，字母(JKLMNOPQR)的值为负，但是在替换步骤中，excel不知道如何将值设为负.

So the problem I cant figure out is how to make the value in a cell negative. As you can see the values for letters (JKLMNOPQR) are negative, however during the replacement step, excel doesnt know how to make the value negative.

我想知道是否可以添加一个步骤，在该步骤中，我可以先将JKLMNOPQR的值设为负，然后再继续执行replace函数和乘法函数.我以为它会在cel中搜索JKLMNOPQR，如果它在单元格中有JKLMNOPQR，它将在单元格前面添加一个负值，然后继续操作.

I was wondering is there a step I can add where I can turn the values that are JKLMNOPQR negative first and then continue you with the replace function and the multiplication function. I was thinking an it would search cel for JKLMNOPQR and if it had JKLMNOPQR in the cell it would add a negative value in front of the cell and continue you on.

非常感谢，很抱歉，我是新来的.

Thanks in advanced, sorry I am new to this.

Private Function DoReplace(Text As String) As String
Dim ReplacedValue As String
ReplacedValue = Text

ReplacedValue = Replace(ReplacedValue, "A", "1")
ReplacedValue = Replace(ReplacedValue, "B", "2")
ReplacedValue = Replace(ReplacedValue, "C", "3")
ReplacedValue = Replace(ReplacedValue, "D", "4")
ReplacedValue = Replace(ReplacedValue, "E", "5")
ReplacedValue = Replace(ReplacedValue, "F", "6")
ReplacedValue = Replace(ReplacedValue, "G", "7")
ReplacedValue = Replace(ReplacedValue, "H", "8")
ReplacedValue = Replace(ReplacedValue, "I", "9")
ReplacedValue = Replace(ReplacedValue, "{", "0")
ReplacedValue = Replace(ReplacedValue, "}", "-0")
ReplacedValue = Replace(ReplacedValue, "J", "-1")
ReplacedValue = Replace(ReplacedValue, "K", "-2")
ReplacedValue = Replace(ReplacedValue, "L", "-3")
ReplacedValue = Replace(ReplacedValue, "M", "-4")
ReplacedValue = Replace(ReplacedValue, "N", "-5")
ReplacedValue = Replace(ReplacedValue, "O", "-6")
ReplacedValue = Replace(ReplacedValue, "P", "-7")
ReplacedValue = Replace(ReplacedValue, "Q", "-8")
ReplacedValue = Replace(ReplacedValue, "R", "-9")



DoReplace = ReplacedValue
End Function




Private Sub MagicButton_Click()
Dim Text As String, CalculatedValue As Double

For Each cell In Worksheets("Sheet1").Range("A1:Z100").Cells
    If cell.Value <> "" Then
        Text = DoReplace(cell.Value)
        CalculatedValue = Val(Text) * 0.01
 Worksheets("Sheet2").Cells(cell.Row, cell.Column).Value =  CalculatedValue
    End If
Next
End Sub

推荐答案

您可以使用instr()首先检查字符是否存在.如果是这样，则它将减号放在该值的前面.

You can use instr() to first check if the character is present. If so, then then it puts the minus sign in front of the value.

ReplacedValue = Replace(ReplacedValue, "A", "1")
ReplacedValue = Replace(ReplacedValue, "B", "2")
ReplacedValue = Replace(ReplacedValue, "C", "3")
ReplacedValue = Replace(ReplacedValue, "D", "4")
ReplacedValue = Replace(ReplacedValue, "E", "5")
ReplacedValue = Replace(ReplacedValue, "F", "6")
ReplacedValue = Replace(ReplacedValue, "G", "7")
ReplacedValue = Replace(ReplacedValue, "H", "8")
ReplacedValue = Replace(ReplacedValue, "I", "9")
ReplacedValue = Replace(ReplacedValue, "{", "0")

If InStr(ReplacedValue, "}") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "}", "0")
End If
If InStr(ReplacedValue, "J") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "J", "1")
End If
If InStr(ReplacedValue, "K") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "K", "2")
End If
If InStr(ReplacedValue, "L") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "L", "3")
End If
If InStr(ReplacedValue, "M") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "M", "4")
End If
If InStr(ReplacedValue, "N") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "N", "5")
End If
If InStr(ReplacedValue, "O") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "O", "6")
End If
If InStr(ReplacedValue, "P") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "P", "7")
End If
If InStr(ReplacedValue, "Q") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "Q", "8")
End If
If InStr(ReplacedValue, "R") <> 0 Then
    ReplacedValue = "-" & Replace(ReplacedValue, "R", "9")
End If 

这篇关于替换函数和负值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！