Excel/VBA-如何在每N个字符的字符串中插入一个字符 [英] Excel/VBA - How to insert a character in a string every N characters
问题描述
我有一个报告,报告在导出时将单个单元格中的订单号(始终为7位数字)显示为一个字符串.例如:订单1234567和9876543在单个单元格中将显示为12345679876543.每个单元格没有最大订单数,每个单元格都不同.
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位添加一个字符,以便以后可以在列中输入文本?
Is there any way that I can add a character every 7 digits so that I can do a text to columns afterwards?
推荐答案
为避免使用冗长而复杂的公式,建议使用VBA.
To avoid using a long and complicated formula, I'd suggest using 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
这篇关于Excel/VBA-如何在每N个字符的字符串中插入一个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!