VBA将单元格值复制到另一个单元格文本字符串中

I am managing inventory in Excel. I have 2 columns to work with:

我在Excel中管理库存。我有2列可供使用:

1. product name
2. packaging info

The table looks like this

表格看起来像这样

product name             packaging info
BLISE FACEWASH           50GM
IVREA CREAM              60GM
IVREA CREAM              30GM
IVREA SHAMPOO            30ML

I want to add the value in packaging info into the product name, but I want to add it before the last word in product name.

我想将包装信息中的值添加到产品名称中,但我想在产品名称的最后一个单词之前添加它。

Desired result:

product name                 packaging info
BLISE 50GM FACEWASH          50GM
IVREA 60GM CREAM             60GM
IVREA 30GM CREAM             30GM
IVREA 30ML SHAMPOO           30ML

I tried concatenate but it does not help and makes it a lot more complicated.

我尝试连接,但它没有帮助,使它变得更复杂。

I have been trying this with macros as it seems doable.

我一直在尝试使用宏,因为它似乎可行。

1. copy value in column 2

复制第2列中的值

2. move to left cell

搬到左边的牢房

3. skip 1 word from right (Ctrl+Left arrow)

从右边跳过1个字(Ctrl +向左箭头)

4. paste value
5. add a space

添加一个空间

6. go to next row

转到下一行

All the cells in product name end up with same values. Where I am going wrong?

产品名称中的所有单元格都以相同的值结束。我哪里错了?

If not with macros, what would be the easiest way to do this?

如果没有宏,最简单的方法是什么?

By the way: the sheet contains 10 columns, 4500+ rows.

顺便说一句:工作表包含10列,4500多行。

1 个解决方案

#1

VBA; Assumes headers are in A1/B1:

VBA;假设标题在A1 / B1中:

Sub MergeCols
    Dim cell As Range, pos As Long

    Set cell = Range("A2")

    Do Until cell.Value = ""
        pos = InStrRev(cell.Value, " ")
        If (pos > 0) Then
            cell.Value = Left$(cell.Value, pos) & cell.Offset(0, 1).Value & Mid$(cell.Value, pos)
        Else
            cell.Value = cell.Value & " " & cell.Offset(0, 1).Value
        End If
        Set cell = cell.Offset(1, 0)
    Loop
End Sub