如何在excel单元格中添加数字？

I have a list of 100 names in column A. For example John, Jacob, James.

我在A栏中列出了100个名字。例如John,Jacob,James。

I would like to add the numbers 1-99999 to each cell in column A.

我想将数字1-99999添加到A列中的每个单元格。

I have the list of numbers, how can I add them to each cell to output like this ? John1 John2 John3 ... Jacob1 Jacob2 Jacob3

我有数字列表,如何将它们添加到每个单元格以输出这样的? John1 John2 John3 ... Jacob1 Jacob2 Jacob3

3 个解决方案

#1

If you place the following formula in Cell B1 and copy down for about 1 million rows, you should get the answer you require:

如果您将以下公式放在单元格B1中并向下复制约100万行,您应该得到您需要的答案:

=INDEX(A:A, INT((ROW() - 1) / 99999) + 1) & (MOD(ROW() - 1, 99999) + 1)

#2

This is a comment on copying down as an answer as I don't have enough reputation

这是一个关于复制作为答案的评论,因为我没有足够的声誉

if you place the formula provided by Phylogenesis in to the first cell in the column next to the column with your data the move your mouse to the bottom right hand corner of the highlighted cell the cursor will change in to a cross. Double click the mouse and this will automatically add the formula to all the appropriate cells - assuming there are no blanks

如果将Phylogenesis提供的公式放入带有数据的列旁边的列中的第一个单元格,则将鼠标移动到突出显示的单元格的右下角,光标将变为十字形。双击鼠标,这将自动将公式添加到所有适当的单元格 - 假设没有空格

#3

Try this small macro:

试试这个小宏:

Sub NumberNames()
    Dim jacob As Long, john As Long, james As Long
    Dim i As Long
    jacob = 1
    james = 1
    john = 1
    For i = 1 To 100
        With Cells(i, 1)
            v = .Value
            If v = "John" Then
                .Value = .Value & john
                john = john + 1
            End If
            If v = "Jacob" Then
                .Value = .Value & jacob
                jacob = jacob + 1
            End If
            If v = "James" Then
                .Value = .Value & james
                james = james + 1
            End If
        End With
    Next i
End Sub

if your data starts out like:

如果您的数据开头如下:

James
Jacob
John
James
John
John
James
John
James
John
Jacob
John
James
James
John
John
James
John
James
James

it will end up like:

它最终会像:

James1
Jacob1
John1
James2
John2
John3
James3
John4
James4
John5
Jacob2
John6
James5
James6
John7
John8
James7
John9
James8
James9

#1