Working with strings
 

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
-----Original Message-----
I am using VBA in Excel XP with Windows 2000.

I have a series of variable length strings in the
following format:

[Some Text] [account number] [some more text]

in actuality, the above pattern may appear as:

xltzy tmv olw 9908472 Concentration Account (USD)
okplk rtes 007552642366 Pension Account (USD)
lmv 65654237 Retirement Account (USD)

Each of the three component pieces (text and account
numbers) above are variable in length, including the
digits in the account number (which is all values).

I need an efficient method to extract the account number
and the last bit of text. Results for the above example
should look like:

Column 1 Column 2
9908472 Concentration Account
007552642366 Pension Account
65654237 Retirement Account

Do I need to loop through each character of each string
to
find the account or is there a better way?

Your help and example code would be most
appreciated...thanks in advance.
.