我想知道如何将列中的分隔值转换为特定格式。例如,如果我在一个单元格/列中有以下值“黑色;深蓝色;浅蓝色”,并希望将其转换为“黑色:0:0:0:1 |深蓝色:0:0:0:2 |浅蓝色:0:0:0:3”,那么实现此结果的最佳方法是什么?
答案1
我的时间一文不值。
Sub delimitAddNum()
'e.g. input: Black;Dark Blue;Light Blue
'e.g. output: Black:0:0:0:1|Dark Blue:0:0:0:2|Light Blue:0:0:0:3
theStr = ActiveCell.Value
If InStr(theStr, ";") = False Then Exit Sub
theSplit = Split(theStr, ";")
theResult = ""
For i = 0 To UBound(theSplit)
If i < UBound(theSplit) Then
theResult = theResult & theSplit(i) & ":0:0:0:" & (i + 1) & "|"
Else
theResult = theResult & theSplit(i) & ":0:0:0:" & i + 1
End If
Next i
theChk = MsgBox("Do you want to overwrite cell " & Replace(ActiveCell.Address, "$", "") & _
" with the following result?" & vbNewLine & vbNewLine & _
theResult, vbYesNo, "Delimiter, Number Thingy")
If theChk = vbYes Then ActiveCell.Value = theResult
End Sub