Excel/VBA - 如何在字符串中每 N 个字符插入一个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43249900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel/VBA - How to insert a character in a string every N characters
提问by Jonorl
I have a report that when exported it displays order numbers (who are always 7 digit long) in a single cell as one single string. Eg: orders 1234567 and 9876543 will appear as 12345679876543 in a single cell. There isn't a maximum number of orders per cell, it varies on every cell.
我有一份报告，在导出时，它在单个单元格中将订单号（始终为 7 位数字）显示为一个字符串。例如：订单 1234567 和 9876543 在单个单元格中将显示为 12345679876543。每个单元格没有最大订单数，每个单元格都不同。
Is there any way that I can add a character every 7 digits so that I can do a text to columns afterwards?
有什么方法可以每 7 位数字添加一个字符，以便之后可以在列中添加文本？
回答by CallumDA
To avoid using a long and complicated formula, I'd suggest using VBA.
为了避免使用冗长而复杂的公式，我建议使用 VBA。
Paste the code below into a standard module and then you can use the formula like this on the worksheet:
将下面的代码粘贴到标准模块中，然后您可以在工作表上使用这样的公式：
=InsertPipe(A1,7)
Function InsertPipe(s As String, interval As Long)
If interval < 1 Then Exit Function
Dim i As Long, result As String
For i = 1 To Len(s) Step interval
On Error Resume Next
result = result & Left(s, interval) & "|"
s = Mid(s, interval + 1, Len(s) - interval)
Next i
InsertPipe = Left(result, Len(result) - 1)
End Function
回答by krib
You could use CONCATENATE.
你可以使用CONCATENATE.
Ex if values are in two cells: =CONCATENATE(A1,",",B1)
例如，如果值在两个单元格中： =CONCATENATE(A1,",",B1)
Ex if values are in one cell =IF(LEN(A1)>7,CONCATENATE(LEFT(A1,7),",",MID(A1,8,100)))
Ex 如果值在一个单元格中 =IF(LEN(A1)>7,CONCATENATE(LEFT(A1,7),",",MID(A1,8,100)))
EDIT ADD
编辑 添加
VBA-Code you could use (found a while ago)
您可以使用的 VBA 代码（不久前发现）
Sub AddACharacter()
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Dim Row As Integer
Dim Char As String
Dim Index As Integer
Dim arr As Variant
Dim Val As String
Dim OutVal As String
Dim Num As Integer
xTitleId = "Add a character"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address,Type:=8)
Row = Application.InputBox("Number of characters :", xTitleId, Type:=1)
Char = Application.InputBox("Specify a character :", xTitleId, Type:=2)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
Num = 1
For Each Rng In InputRng
Val = Rng.Value
OutVal = ""
For Index = 1 To VBA.Len(Val)
If Index Mod Row = 0 And Index <> VBA.Len(Val) Then
OutVal = OutVal + VBA.Mid(Val, Index, 1) + Char
Else
OutVal = OutVal + VBA.Mid(Val, Index, 1)
End If
Next
OutRng.Cells(Num, 1).Value = OutVal
Num = Num + 1
Next
End Sub
回答by Nathan_Sav
You can use a formula copied accross and down, but would be finite in relation to splitting expected, you could use something along these lines, in b1 to z1 in my example.
您可以使用交叉和向下复制的公式，但对于预期的拆分来说是有限的，您可以使用这些方法，在我的示例中的 b1 到 z1 中。
=MID($A1,IF(COLUMN()-2=0,1,((COLUMN()-2)*7)+1),7)
=MID($A1,IF(COLUMN()-2=0,1,((COLUMN()-2)*7)+1),7)
回答by Subodh Tiwari sktneer
Assuming the string is in A2, then try this... In B2
假设字符串在 A2 中，然后试试这个...在 B2 中
=SUBSTITUTE(A2,LEFT(A2,7),LEFT(A2,7)&"|")
The above formula will insert a "|" after seven characters.
上面的公式会插入一个“|” 七个字符后。
