Excel:如何根据另一列中的文本值在列中创建数字? [英] Excel: how to create a number in a column according to a text value in other column?

问题描述

我有一个包含重复值的文本列，例如:

I have a text column with repeated values, for example:

   |    A    | 
---|---------|
 1 | emails  |
 2 |  foo    |
 3 |  foo    |
 4 |  bar    |
 5 |  bar    |
 6 |  stuff  |
 7 |  stuff  |
 8 |  stuff  |

我想做的是在另一列中添加数字，以便每个数字都与第一列中的值匹配，例如:

What I would like to do is to have another column with numbers, so that each number matches the value in the first column, for example:

   |    A    |    B    | 
---|---------|---------|
 1 | emails  | number  |
 2 |  foo    |    1    |
 3 |  foo    |    1    |
 4 |  bar    |    2    |
 5 |  bar    |    2    |
 6 |  stuff  |    3    |
 7 |  stuff  |    3    |
 8 |  stuff  |    3    |

推荐答案

取决于您所指的值-

如果您的意思是重复组"，则这样的宏可能会起作用-

If you mean "repeating group" then a macro like this may work -

Sub Check()
    Dim start As Integer
    start = 1
    For Row = 2 To 12
        Cells.Item(Row, 2) = start
        If Cells.Item(Row, 1) <> Cells.Item(Row + 1, 1) Then
            start = start + 1
        End If
    Next
End Sub

如果您的意思是在A列中每个值的末尾取数字"，则可能有效-

If you mean "take the number at the end of each value in column A" then this may work -

Sub Check()
    Dim i As Integer
    Dim value As String
    Dim number As String
    Dim c As String
    For Row = 2 To 12
        number = ""
        value = Cells.Item(Row, 1)
        For i = 1 To Len(value)
            c = Mid(value, i, 1)
            If IsNumeric(c) Then
                number = number & c
            End If
        Next
        Cells.Item(Row, 2) = number
    Next
End Sub

编辑-根据OP的评论，我猜它们的意思是重复组"，但我将在此处保留两个代码示例.

Edit - According to the OP's comment, I'm guessing they mean "repeating group" but I'll leave both code samples here.

编辑-将代码复制并粘贴到excel Visual Basic编辑器中，然后单击播放.将常数2和12替换为开始行和结束行的值.

Edit - Copy and paste the code into the excel Visual Basic Editor and click play. Replace the constants 2 and 12 with the values of your start and end rows.

这篇关于Excel:如何根据另一列中的文本值在列中创建数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！