我正在尝试找到一个公式来从可以包含附加信息的单元格中提取帐号。帐号始终以“A”或“U”开头,然后有 6 个数字,例如 A123456 或 U654321。
单元格可以只包含帐号,也可以包含其他数据,例如“ADT CASH DEPOSIT A235999”或“FNB APP PAYMENT FROM A230275”
答案1
这是一个替代函数,它以相反的方式(首字母,然后是数字)进行搜索,避免在数字无法容纳的地方进行搜索,并且不区分大小写:
Public Function AccountNo(thiscell As Range) As String
Dim numlen As Integer, cellText As String
Dim i As Long, rest As String, firstletter As String
AccountNo = ""
numlen = 6
cellText = thiscell.Text
For i = 1 To Len(cellText) - numlen
firstletter = UCase(Mid(cellText, i, 1))
If (firstletter = "A" Or firstletter = "U") Then
rest = Mid(cellText, i + 1, numlen)
If IsNumeric(rest) Then
AccountNo = firstletter & rest
Exit Function
End If
End If
Next i
End Function
答案2
此 UDF 可检测帐号,无论它位于何处:
Public Function accounts(cellText As String)
accounts = ""
numberlength = 6
posText = 0
lenText = Len(cellText)
For i = 1 To lenText
midText = Mid(cellText, i, numberlength)
If IsNumeric(midText) = True Then
posText = i
letterText = Mid(cellText, posText - 1, 1)
If (letterText = "A" Or letterText = "U") Then
accounts = Mid(cellText, posText - 1, numberlength + 1)
i = lenText
End If
End If
Next i
End Function
复制代码,打开Visual Basic / Macros,在本工作簿插入一个新的模块并将代码粘贴在右侧。
要使用它,如果文本在单元格中A1
,则B1
必须在单元格中放置=accounts(A1)
。