Add one more "Exit For" statement please

"PY & Associates" wrote in message
...
The structure of the data is AAA####.####
therefore we would try (not tested)

for i = 2 to 8
if isnumeric(mid(Nr, i, 1)) then
j = i
part1=left(Nr, i-1)
else if mid(Nr, i, 1)="." then
part2=mid(Nr, j, i-j)
part3=mid(Nr, i, 30)
end if
next i

"Casper" wrote in message
...
Neither of those solutions work, exactly, because they assume too much
about
how the data is structured. However, the idea of breaking the call
number
down into multiple fields holds, I think, the key to the problem.

Incidentally, here's a description that's as good as any on how the LoC
call
numbers are structured:
http://library.dts.edu/Pages/RM/Helps/lc_call.shtml

So what I'd like to do is break the data down into three columns. The
first
column will have the initial 1-3 letters, the next will have the number
section, and the third will have the period and everything after. I'll
format columns 1 and 3 as text, and 2 as number, so I can tell Excel to
sort
first by the first column, next by the second, and next by the third.

Now the challenge becomes, "How can I identify a variable number of
letters
and copy them to the first column, and variable number of numbers after
letters and before a decimal point and move them to a second column, and
the
decimal point and everything after into a third column?"

The third column ought to look like:
=RIGHT(B1,(LEN(B1)-SEARCH(.,B1,1)))

The first two are going to involve ISTEXT and ISNUMBER with IFs, I
suspect,
but I'm less sure how to structure those. Thanks in advance for any
advice
you can give!