Excel，VB - 字符操作&改组内部数组 [英] Excel, VB - Character Operations & Date Reformating Inside Array
问题描述
ISSUE
在特定字段中包含许多不正确或不均匀输入日期的大型数据集。约有6万条记录输入日期约有19种不同的方式。一些条目完全无效，必须被丢弃，其他条目必须格式正确。我将范围加载到数组中，然后执行操作。 我已经写出了我相信我需要的操作，但是我需要一些帮助来实现他们的工作。见下面的代码;
当前问题
对于54天的条目，以下代码应该只将字符留在空格的左边，然后检查它们是否是数字。如果它们是，它会保留它们，如果它们不是空的数组元素。 在实践中，没有任何事情发生，我仍然在数组元素中有完整的条目？
ElseIf delType =StringAnd Len（del（i，1））< 10和InStrRev（del（i，1），）然后'从2194天或23 DPDpre>
del（i，1）= Left（del（i，1），Len （i，1）） - InStrRev（del（i，1），））
如果IsNumeric（del（i，1））= False那么'如果空格左边的字符不是数字，丢弃
del（i，1）= Empty
Else
del（i，1）= Format（（CLng（Range（E& i + 1）.Value） Abs（del（i，1））），mm / dd / yy）'拉取订单日期并从中扣除违约日期的日期
结束If
入场模式
SEPT。 25，20 （无年，无年！删除。）
SEPT （无年，无用，删除。）
N / A （垃圾！删除。）
长时间AG （什么mor子认为这是一个好主意，删除。）
6月30日，200日（显然该字段只能保留12个字符，删除。）
CHARGED OFF （无用，删除。）
94天（取空格之前的所有字符，并从包含订单日期的其他字段中减去获得违约日期。）
94 DPD DPD in someone bright mind代表天过期我相信，同上。）
2008-7-15 12 （不知道什么额外的数字，把所有的字符放在前面空格和变换。）
INVALID （删除。）
空白（不执行任何操作。）
12282009 （使用LEFT和RIGHT和CONCATENATE嵌套/之间）。
92020 11 （添加前导零，然后与上述相同。）
92410 （添加前导零，这将转换为09/24/10）
41261 （自31/12/1899以来的天数，将转换为12/08/12）
1023 （自犯罪以来的天数，
452 （与上述相同）
12 （与上述相同） 。）
1432.84 （货币价值错误地被低智商输入。删除。）
代码（正在进行中）
'在犯罪日期执行家务管理
列（AH：AH）。选择
Selection.NumberFormat =0
Selection.Copy
Selection.PasteSpecial粘贴：= xlValues ，操作：= xlNone，SkipBlanks：= _
True，Transpose：= False
Dim del（）
ReDim del（1 To importwsRowCount，1 To 1）
del =范围（AH1：AH& importwsRowCount）.Value
Dim delChars As Long
Dim delType As String
For i = LBound（del，1）To UBound（del，1）
delChars = Len（del（i，1））'确定条目的长度
如果IsNumeric（del（i，1））= True Then'确定条目的数据类型
delType =Numeric
Else
delType =String
End If
如果InStr（del（i，1），。）然后'删除像142.84
del我，1）=空
ElseIf InStr（del（i，1），*）然后'删除*** INVALID ***条目
del（i，1）=空
E lseIf delChars = 12 Then'删除超出该字段的12个字符限制的所有条目，并将其截断
del（i，1）= Empty
ElseIf delType =StringAnd Len（del i，1））< 10和InStrRev（del（i，1），）然后'从2194天或23 DPD
del（i，1）= Left（del（i，1），Len （i，1）） - InStrRev（del（i，1），））
如果IsNumeric（del（i，1））= False那么'如果空格左边的字符不是数字，丢弃
del（i，1）= Empty
Else
del（i，1）= Format（（CLng（Range（E& i + 1）.Value） Abs（del（i，1））），mm / dd / yy）'拉取订单日期并从中扣除违约日期的日期
End If
ElseIf delType =NumericAnd Len del（i，1））= 5然后
如果del（i，1）> CLNG（Date）然后'值大于今天的日期，不正确的日期，需要字符操纵和/添加
del（i，1）=格式（del（i，1），000000）'零（del（i，1），2），Left（del（i，1），2），Right（Left（del（i，1），2 ）（4））'获取年份，然后是月份，然后序列化
Else
del（i，1）=格式（del（i，1），mm / dd / yy正确形成日期，只需要格式转换
End If
ElseIf delType =NumericAnd（delChars = 7或delChars = 8）然后
如果delChars = 7然后
del i，1）=格式（del（i，1），00000000）'添加前导零
如果
del（i，1）= DateSerial（Right（del（i，1） 4），Left（del（i，1），2），Right（Left（del（i，1），2），6））'Grab year，then month，then day for serialize
ElseIf delType = 数字和delChars 5然后
del（i，1）=格式（（CLng（范围（E& i + 1）） - Abs（del（i，1））），mm / dd / yy
End If
Next i
Set delRange = Range（AJ1：AJ& importwsRowCount）
iWS.names.Add名称：=dRange，RefersTo：= delRange
Range（dRange）。Value = del'将数组写入工作表
解决方案54天包含7个字符，所以
Len（54天）是7
空格字符出现在位置3，
InStrRev（54 Days，）返回3
所以，我们有
左（54天，7 - 3）生成54 D显然不是数字
可能尝试
Left（del（i，1），InStrRev（del（i，1），） - 1）而不是ISSUE
Large dataset with many improperly or non-uniformly entered dates in a specific field. There are about 19 different ways the dates have been entered in about 60,000 records. Some entries are completely invalid and must be discarded, others must be formatted properly. I am loading the range into an array and then performing the operations. I wrote out the operations as I believe I need, however I need some help in actually getting them working. See code below; error in comments.
CURRENT PROBLEM
For entries like '54 Days' the following code should only take the characters to the left of the space, and then check if they are numeric. If they are, it keeps them, if they are not it empties that array element. In practice, nothing is happening, I still have the full entry in the array element?
ElseIf delType = "String" And Len(del(i, 1)) < 10 And InStrRev(del(i, 1), " ") Then 'Takes the number from entries like 2194 Days or 23 DPD del(i, 1) = Left(del(i, 1), Len(del(i, 1)) - InStrRev(del(i, 1), " ")) If IsNumeric(del(i, 1)) = False Then 'If the characters to the left of the space are not numbers, discard del(i, 1) = Empty Else del(i, 1) = Format((CLng(Range("E" & i + 1).Value) - Abs(del(i, 1))), "mm/dd/yy") 'Pull order date and subtract days from it for delinquency date End If
ENTRY TEMPLATES
SEPT. 25, 20 (No year, no year! Delete.)
SEPT (No year, useless, delete.)
N/A (Rubbish! Deleted.)
LONG TIME AG (What moron thought this was a good idea, delete.)
JUNE 30, 200 (Apparently the field will only hold 12 characters, delete.)
CHARGED OFF (Useless, delete.)
94 DAYS (Take all characters preceding space and subtract from other field containing order date to obtain delinquent date.)
94 DPD (DPD in someones bright mind stands for Days Past Due I believe. Same as above.)
2008-7-15 12 (Not sure what additional number is, take all characters before space and transform.)
INVALID (Delete.)
BLANK (Do nothing.)
12282009 (Use nested LEFT and RIGHT and CONCATENATE with / in between.)
9202011 (Add leading zero, then same as above.)
92410 (Add leading zero, this will transform to 09/24/10)
41261 (Days since 31/12/1899, this will transform to 12/08/12)
1023 (Days since delinquent, subtract from ORDER DATE to get delinquent date.)
452 (Same as above.)
12 (Same as above.)
1432.84 (Monetary value, mistakenly entered by low IQ lackey. Delete.)CODE (WORK IN PROGRESS)
'Perform housekeeping on delinquency date Columns("AH:AH").Select Selection.NumberFormat = "0" Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ True, Transpose:=False Dim del() ReDim del(1 To importwsRowCount, 1 To 1) del = Range("AH1:AH" & importwsRowCount).Value Dim delChars As Long Dim delType As String For i = LBound(del, 1) To UBound(del, 1) delChars = Len(del(i, 1)) 'Determine length of entry If IsNumeric(del(i, 1)) = True Then 'Determine datatype of entry delType = "Numeric" Else delType = "String" End If If InStr(del(i, 1), ".") Then 'Removes monetary entries like 142.84 del(i, 1) = Empty ElseIf InStr(del(i, 1), "*") Then 'Removes ***INVALID*** entries del(i, 1) = Empty ElseIf delChars = 12 Then 'Removes all entries that extend beyond the 12 character limit of the field and get cut off del(i, 1) = Empty ElseIf delType = "String" And Len(del(i, 1)) < 10 And InStrRev(del(i, 1), " ") Then 'Takes the number from entries like 2194 Days or 23 DPD del(i, 1) = Left(del(i, 1), Len(del(i, 1)) - InStrRev(del(i, 1), " ")) If IsNumeric(del(i, 1)) = False Then 'If the characters to the left of the space are not numbers, discard del(i, 1) = Empty Else del(i, 1) = Format((CLng(Range("E" & i + 1).Value) - Abs(del(i, 1))), "mm/dd/yy") 'Pull order date and subtract days from it for delinquency date End If ElseIf delType = "Numeric" And Len(del(i, 1)) = 5 Then If del(i, 1) > CLng(Date) Then 'Value is greater than todays date, improperly formated date that needs character manipulation and / added del(i, 1) = Format(del(i, 1), "000000") 'Add leading zero del(i, 1) = DateSerial(Right(del(i, 1), 2), Left(del(i, 1), 2), Right(Left(del(i, 1), 2), 4)) 'Grab year, then month, then day for serialize Else del(i, 1) = Format(del(i, 1), "mm/dd/yy") 'Properly formated date that just needs format conversion End If ElseIf delType = "Numeric" And (delChars = 7 Or delChars = 8) Then If delChars = 7 Then del(i, 1) = Format(del(i, 1), "00000000") 'Add leading zero End If del(i, 1) = DateSerial(Right(del(i, 1), 4), Left(del(i, 1), 2), Right(Left(del(i, 1), 2), 6)) 'Grab year, then month, then day for serialize ElseIf delType = "Numeric" And delChars < 5 Then del(i, 1) = Format((CLng(Range("E" & i + 1)) - Abs(del(i, 1))), "mm/dd/yy") End If Next i Set delRange = Range("AJ1:AJ" & importwsRowCount) iWS.names.Add Name:="dRange", RefersTo:=delRange Range("dRange").Value = del 'Write array to worksheet
解决方案"54 Days" contains 7 characters so
Len("54 Days")is 7The space character occurs in position 3 and
InStrRev("54 Days", " ")returns 3So, we have
Left("54 Days", 7 - 3)which produces"54 D"which is obviously not numericPerhaps try
Left(del(i, 1), InStrRev(del(i, 1), " ") - 1)instead这篇关于Excel，VB - 字符操作&改组内部数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
