Working with strings

[Andrew Lenczycki]

Here is one way to do it (by using user defined
functions). It can also be done using strictly VBA
macros. It was not clear where and how the data comes
into the worksheet. I've made the assumption it's in a
column (A) in a worksheet. By using the two user defined
functions below, one in column B, the other in column C,
the desired result is extracted from the raw data.

Function FindAccount(txt As String)
'find the account number in the text
Dim i As Integer
Dim j As Integer
Dim AcctStart As Integer
Dim AcctEnd As Integer

For i = 1 To Len(txt)
If IsNumeric(Mid(txt, i, 1)) Then
AcctStart = i
Exit For
End If
Next i
For j = AcctStart To Len(txt)
If Mid(txt, j, 1) = " " Then
AcctEnd = j - 1
Exit For
End If
Next j
FindAccount = Mid(txt, AcctStart, (AcctEnd -
AcctStart))
End Function


Function FindAccountName(txt As String)
'find the account name in the text
Dim i As Integer
Dim j As Integer
Dim AcctStart As Integer
Dim AcctEnd As Integer

For i = 1 To Len(txt)
If IsNumeric(Mid(txt, i, 1)) Then
AcctStart = i
Exit For
End If
Next i
For j = AcctStart To Len(txt)
If Mid(txt, j, 1) = " " Then
AcctEnd = j - 1
Exit For
End If
Next j
FindAccountName = Mid(txt, (AcctEnd + 2), Len(txt) -
(AcctEnd + 1))
End Function

Hope the above was of use.

Andrew Lenczycki