How do I sort Library of Congress call numbers in Excel?

[Guest]

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.

 

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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.

 

[Guest]

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!

 

[PY & Associates]

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

 

[PY & Associates]

Add one more "Exit For" statement please

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

 

[PY & Associates]

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