Working with strings
 

On Wed, 30 Jul 2003 06:58:08 -0700, "MacroMan" wrote:

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.

Well, you could do it with worksheet formulas:

Account Number (must be *array-entered*):

=MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),
FIND(" ",A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))
-MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

To array-enter, after pasting in the above, hold down <ctrl<shift while
hitting <enter. XL will place braces {...} around the formula.

Account Type:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))-2))+1,FIND("(",A1)-
FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2))-2)

Assumptions in the above:

1. Original data is in A1.
2. Numbers are first seen in the account number segment.
3. The format of the last three segments is as you present with account type;
<space; a word like Account; <space; parenthesis "("; no more spaces.





--ron