练成替代多个实例在不同的字符位置
问题描述:
我有从SQL数据库与周围6500行与一列的数据集与包含多个值的字段。练成替代多个实例在不同的字符位置
例
Cell A1 3.00GGG1.00DDD2.00EEE1.00DEF5.00TTT
Cell A2 10.00ABC1.00DDD2.00EEE1.00DEF
.00是字符串中唯一一致的一系列字符
我需要引入一个“”能够用文本工具列中的值分成不同列之后
并发症是将小数点左边的数目可以改变,并且可以是10或更大,以便逗号的位置可以是1个或2位置的十进制
左侧所以我需要一个实现
Cell A1 ,3.00GGG,1.00DDD,2.00EEE,1.00DEF,5.00TTT
Cell A2 ,10.00ABC,1.00DDD,2.00EEE,1.00DEF
要最终通过使用文本到
列达到3.00GGG 1.00DDD 2.00EEE 1.00DEF 5.00TTT
10.00ABC 1.00DDD 2.00EEE 1.00DEF
没有人有任何想法如何通过一个公式或宏来实现这一目标?
谢谢!
答
我用RegEx写了一些东西给你。它确实存在漏洞,试试这个,如果它不起作用,我们将处理这个漏洞。
call findMatch("3.00GGG1.00DDD2.00EEE1.00DEF5.00TTT ","[A-Za-z]\d")
Function findMatch(strValue, strPattern)
Dim objRegEx
Dim objPosition
Dim strPosition
strNewVal = strValue
'find out how many different strings will be there
countOfStrings = len(strNewVal) - len(replace(strNewVal, ".", ""))
strCompiledString = ""
'Create regular expression
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Pattern = strPattern
'loop to break the string up and tie it with comma
for i= 1 to countOfStrings
if not objRegEx.Test(strValue) then
'if condition to check whether or not there are any matches
strCompiledString = strCompiledString & "," & strValue
exit for
end if
Set objPosition = objRegEx.Execute(strValue)
strPosition = objPosition(0).FirstIndex
findMatch= strPosition
'based on match index, add the left part of the string to the variable strCompiledString
if(strCompiledString="") then
strCompiledString = mid(strValue,1,strPosition+1)
else
strCompiledString = strCompiledString & "," & mid(strValue,1,strPosition+1)
end if
'remove the already processed substring from the main string
strValue = mid(strValue,strPosition+2)
next
'now we have it
msgbox("BOOOOM : " & strCompiledString)
End Function
答
我从来没有想过我会这样说,但对我来说它实际上使用Word。
将行复制到Word文档。打开查找/替换(CTRL + H)
,点击More>>
打开高级菜单并勾选框Use wildcards
。
然后键入找到([0-9]{1,2}.)
并替换为,\1
替换。点击全部替换并将所有内容复制回Excel表单。
答
我修改的UDF我写了一个字符串获得的第n个号码,获得第n个数字加上字母: -
Function GetNthNumber(s As String, n As Integer) As String
Dim c, testNumber, number As String
s = s & "0"
Dim i, j, count As Integer
Dim inNumber As Boolean
inNumber = False
' Loop through each character of string
For i = 1 To Len(s)
c = Mid(s, i, 1)
' Part of a number - append new character or finish number
If inNumber Then
If Not (IsNumeric(number & c)) Then
inNumber = False
End If
number = number & c
Else
' Not part of a number - start new number or do nothing
If IsNumeric(c) Then
If count = n Or i = Len(s) Then Exit For
inNumber = True
number = c
count = count + 1
Else
number = number + c
End If
End If
Next i
'Return nth number or #Value error
If count >= n Then
GetNthNumber = number
Else
GetNthNumber = CVErr(xlErrValue)
End If
End Function
要使用它,调用函数来获得的第一个数字,和拉跨,直到它出现了错误
=IFERROR(GetNthNumber($A1,COLUMN(A:A)),"")
是GGG等字母,或其他一些非数字字符? –
嗨,他们是信件感谢 – Mre