I'm looking to sort Library of Congress call numbers in Excel. The format of
the cdall numbers is:

Letters Number Period Letter Number

and after that, perhaps more periods, letters and numbers!

The problem seems to be that if I have (sorted):
C3.A40
C25.C25
C124.D45

attempting to sort them as text would list
C124.D45
C25.C25
C3.A40

Which is incorrect. I've been looking at the cell formatting features, and
they seem to only allow me to change how the data is displayed, not how Excel
thinks about the type of data.

Try a helper column with this formula copied down, and sort on that
column.......

=MID(A1,2,FIND(".",A1,1)-2)*1

hth
Vaya con Dios,
Chuck, CABGx3



"Casper" wrote:

> I'm looking to sort Library of Congress call numbers in Excel. The format of
> the cdall numbers is:
>
> Letters Number Period Letter Number
>
> and after that, perhaps more periods, letters and numbers!
>
> The problem seems to be that if I have (sorted):
> C3.A40
> C25.C25
> C124.D45
>
> attempting to sort them as text would list
> C124.D45
> C25.C25
> C3.A40
>
> Which is incorrect. I've been looking at the cell formatting features, and
> they seem to only allow me to change how the data is displayed, not how Excel
> thinks about the type of data.

You will need to convert the codes into a fixed number of characters by
inserting leading zeros, so that your data becomes:

C003.A40
C025.C25
C124.D45

assuming your first set of numbers is limited to 3 and your second set
is 2 digits. You can use SEARCH( ) or FIND( ) to look for the first
period and use this to extract the characters before it, then split and
re-combine the elements in this way. You will need a few helper
columns.

Hope this helps.

Pete

Casper wrote:
> I'm looking to sort Library of Congress call numbers in Excel. The format of
> the cdall numbers is:
>
> Letters Number Period Letter Number
>
> and after that, perhaps more periods, letters and numbers!
>
> The problem seems to be that if I have (sorted):
> C3.A40
> C25.C25
> C124.D45
>
> attempting to sort them as text would list
> C124.D45
> C25.C25
> C3.A40
>
> Which is incorrect. I've been looking at the cell formatting features, and
> they seem to only allow me to change how the data is displayed, not how Excel
> thinks about the type of data.

I think you already have answers for the "how to". I just wanted to say that
you are correct in saying there is a difference in how information is
displayed on a spreadsheet and how Excel "thinks about it" internally.
Always remember that - this difference in 'value' and 'formatted appearance'
is often the souce of confusion. Take a simple date - it may look like
07-JUL-2006 11:58:11 a.m. but internally Excel is seeing it as
38909.4987384259

Kudos for realizing that such a difference exists.

"Casper" wrote:

> I'm looking to sort Library of Congress call numbers in Excel. The format of
> the cdall numbers is:
>
> Letters Number Period Letter Number
>
> and after that, perhaps more periods, letters and numbers!
>
> The problem seems to be that if I have (sorted):
> C3.A40
> C25.C25
> C124.D45
>
> attempting to sort them as text would list
> C124.D45
> C25.C25
> C3.A40
>
> Which is incorrect. I've been looking at the cell formatting features, and
> they seem to only allow me to change how the data is displayed, not how Excel
> thinks about the type of data.

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!

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" <(E-Mail Removed)> wrote in message
news:925BC303-B813-48F5-B9CE-(E-Mail Removed)...
> 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!

Add one more "Exit For" statement please

"PY & Associates" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:925BC303-B813-48F5-B9CE-(E-Mail Removed)...
> > 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!
>
>

Tested now

Sub M()
Nr = ActiveCell
j = 0
For i = 2 To 7
If (j = 0) * IsNumeric(Mid(Nr, i, 1)) Then
j = i
part1 = Left(Nr, i - 1)
Debug.Print part1
Else
If Mid(Nr, i, 1) = "." Then
part2 = Mid(Nr, j, i - j)
part3 = Mid(Nr, i, 30)
Debug.Print part2
Debug.Print part3
Exit For
End If
End If
Next i

End Sub

"PY & Associates" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Add one more "Exit For" statement please
>
> "PY & Associates" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:925BC303-B813-48F5-B9CE-(E-Mail Removed)...
> > > 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!
> >
> >
>
>